Looping recordsets in stored procedures

[multiplex77]

Hi,

I have a complex set of queries I need to invoke in order to get my desired record set, which I'm using a stored procedure for. I need to loop through the recordsets of the first select query, to analyze a second table, but don't know how to look at individual records of that record set.

If I'm using ASP, I would just do...

objRS.Open "SELECT table1_ID, Name1 FROM table1"

While Not objRS.EOF
objRS2.Open "SELECT * FROM table2 " & _
"WHERE ID = " & objRS("table1_ID&quot & _
" Name = '" & objRS("Name1&quot & "'"
objRS.MoveNext
Wend

However, using T-SQL, I don't know how to loop through resultsets. Could someone please help me?

Thanks very much, in advance.

...and then

 

[NathanGriffiths]

Sounds like you need to use a Cursor, e.g.

DECLARE @table1_ID INT
DECLARE @Name1 VARCHAR(50)

DECLARE csSomeCursor CURSOR LOCAL FAST FORWARD FOR
SELECT table1_ID, Name1 FROM table1

OPEN csSomeCursor

FETCH NEXT FROM csSomeCursor INTO @table1_ID, @Name1

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM table2 WHERE ID = @table1_ID AND Name = @Name1
FETCH NEXT FROM csSomeCursor INTO @table1_ID, @Name1
END

CLOSE csSomeCursor
DEALLOCATE csSomeCursor

Hope that helps

Nathan

 

[rt63]

declare @tbl_id <some datatype>, @tbl_name <some datatype>
declare rset1 cursor
for
SELECT table1_ID, Name1 FROM table1

open rset1
fetch rset1 into @tbl_id , @tbl_name
while @@fetch_status = 0
begin
SELECT * FROM table2
WHERE ID = @tbl_id
and Name = @tbl_name
fetch rset1 into @tbl_id , @tbl_name
end
close rset1
deallocate rset1

RT

 

[multiplex77]

IT works! Thanks very much Nathan and RT!!