question

cankrum avatar image
cankrum asked

How do I loop through a result set and populate variables with data??

New to SQL and need some guidance please. I am trying to loop through a data set using the ROW_NUMBER function. I only care what the data is for the first three results in the result set. Here is my query: Select person_id, @GuarTerPayerName = payer_name, @GuarTerPolicyNbr = policy_nbr from ( Select person_id, payer_name, policy_nbr, ROW_NUMBER() OVER ( order by payer_name) as 'Row_Number', * from person_payer where person_id = @GuarPersonID ) as tbl Where tbl.Row_Number = 3 I am receiving the following error, which indicates that I can not populate a variable while using ROW_NUMBER. Grrr.... Msg 141, Level 15, State 1, Procedure NCS_GetGuarantorInfo, Line 51 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Thoughts as to how I can accomplish this?? As I stated, I am NEW to SQL and trying desperately to learn! Thanks in advance for any input.
row_number
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is the sub-query returning multiple rows? Just confirming.
0 Likes 0 ·

1 Answer

· Write an Answer
cankrum avatar image
cankrum answered
Thanks.... I figured it out. Had to use a cursor.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Most likely, that's absolutely the worst way to do this. My question is, why do you need to populate a variable here? Is it to call another stored procedure or ???
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.