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.