Posted by Dan Guzman on 05/22/07 10:33

> 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" <askjavaprogrammers@gmail.com> wrote in message
news:1179827614.563723.255190@b40g2000prd.googlegroups.com...
>
> 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.
>

[Back to original message]