Getting Result of query inside another query

Hi to all,

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

create procedure marks1
as
@. sql1 as varchar(50)

@. sql1=select registerno ,subjectcode from mark;

begin

select * from marksetting where registerno='@.sql1.registerno' and
subjectcode='@.sql1.subjectcode';

end

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

Thanks in Advance.I just need to get two fields from a table and manipulate the results

Quote:

Originally Posted by

in next query of a procedure.I planned to code like what you see
below,

Why not use a single query? For example:

CREATE PROCEDURE dbo.marks1
AS

SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO

To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:

CREATE PROCEDURE dbo.marks1
AS

DECLARE
@.registerno int,
@.subjectcode int

SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;

SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO

If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:

CREATE PROCEDURE dbo.marks1
AS

DECLARE @.results TABLE
(
registerno int,
subjectcode int
);

INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;

SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"meendar" <askjavaprogrammers@.gmail.comwrote in message
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...

Quote:

Originally Posted by

>
Hi to all,
>
I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,
>
create procedure marks1
as
@. sql1 as varchar(50)
>
@. sql1=select registerno ,subjectcode from mark;
>
begin
>
select * from marksetting where registerno='@.sql1.registerno' and
subjectcode='@.sql1.subjectcode';
>
end
>
>
can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.
>
Thanks in Advance.
>

|||On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>
Why not use a single query? For example:
>
CREATE PROCEDURE dbo.marks1
AS
>
SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO
>
To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE
@.registerno int,
@.subjectcode int
>
SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;
>
SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO
>
If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE @.results TABLE
(
registerno int,
subjectcode int
);
>
INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;
>
SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"meendar" <askjavaprogramm...@.gmail.comwrote in message
>
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi to all,

>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>

Quote:

Originally Posted by

begin

>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>

Quote:

Originally Posted by

end

>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>
- Show quoted text -

Thanks Dan!

Indeed it was very helpful to me.|||On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>
Why not use a single query? For example:
>
CREATE PROCEDURE dbo.marks1
AS
>
SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO
>
To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE
@.registerno int,
@.subjectcode int
>
SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;
>
SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO
>
If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE @.results TABLE
(
registerno int,
subjectcode int
);
>
INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;
>
SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"meendar" <askjavaprogramm...@.gmail.comwrote in message
>
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi to all,

>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>

Quote:

Originally Posted by

begin

>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>

Quote:

Originally Posted by

end

>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>
- Show quoted text -

Thanks Dan!

Indeed it was very helpful to me.|||I'm glad I was able to help.

--
Dan Guzman
SQL Server MVP

"meendar" <askjavaprogrammers@.gmail.comwrote in message
news:1179837037.793991.296140@.b40g2000prd.googlegr oups.com...

Quote:

Originally Posted by

On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>>
>Why not use a single query? For example:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>SELECT *
>FROM dbo.marksetting
>JOIN dbo.mark ON
> mark.registerno = marksetting.registerno AND
> mark.subjectcode = marksetting.subjectcode;
>GO
>>
>To answer your question, if the first query returns no more than a single
>row, you could assign the result values to variables for use in the
>second
>query:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>DECLARE
> @.registerno int,
> @.subjectcode int
>>
>SELECT
> @.registerno = registerno,
> @.subjectcode = subjectcode
>FROM dbp.mark;
>>
>SELECT *
>FROM dbo.marksetting
>WHERE
> registerno = @.registerno AND
> subjectcode = @.subjectcode;
>GO
>>
>If the first query might return more than one row, you could store the
>result in a temp table or variable for use in the second query:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>DECLARE @.results TABLE
>(
> registerno int,
> subjectcode int
>);
>>
>INSERT INTO @.results
> SELECT
> registerno,
> subjectcode
> FROM dbo.mark;
>>
>SELECT *
>FROM dbo.marksetting
>JOIN @.results AS r ON
> marksetting.registerno = r.registerno AND
> marksetting.subjectcode = r,subjectcode;
>GO
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"meendar" <askjavaprogramm...@.gmail.comwrote in message
>>
>news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>>
>>
>>
>>
>>

Quote:

Originally Posted by

Hi to all,

>>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>>

Quote:

Originally Posted by

begin

>>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>>

Quote:

Originally Posted by

end

>>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>>
>- Show quoted text -

>
Thanks Dan!
>
Indeed it was very helpful to me.
>