T-SQL cursor, variable is not visible inside loop

Question

I am not sure what is wrong here, please help.

I am trying to loop over set of result and to use id's from set to execute another select query.

DECLARE @MyCursor CURSOR;
DECLARE @ID int;
DECLARE @Name varchar(200)
BEGIN
    SET @MyCursor = CURSOR FOR
    SELECT  ID, Name FROM UserGroups

OPEN @MyCursor 
  FETCH @MyCursor INTO @ID, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT * FROM ErrorToNotify INNER JOIN ErrorMessages ON
    ErrorToNotify.ErrMsgID = ErrorMessages.ID
    INNER JOIN InterfaceErrors ON
    InterfaceErrors.ErrMessageID = ErrorMessages.ID
     WHERE UserGroupID = @ID 
  FETCH NEXT FROM @MyCursor         
END; 

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;

Please post the error message you are getting when running your query. — fez
– fez, Commented Oct 6, 2015 at 10:05

Jakub Lortz · Accepted Answer · 2015-10-06 10:11:05Z

3

Use FETCH NEXT FROM @MyCursor INTO @ID, @Name before the loop and at the end of the loop

Edit

My answer solves your syntax errors, but the correct approach is not to use a cursor. Check Rahul's answer.

edited Oct 6, 2015 at 10:11

answered Oct 6, 2015 at 10:01

Jakub Lortz

14.9k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marko Radosevic Over a year ago

Thank you so much. Will accept your answer in few minutes.

Rahul · Accepted Answer · 2015-10-06 10:07:07Z

1

You certainly don't need a cursor here rather just need to do another extra join with UserGroups table like below. Moreover, why you are fetching Name column in cursor since you are not using it anywhere.

SELECT * FROM ErrorToNotify 
INNER JOIN ErrorMessages ON ErrorToNotify.ErrMsgID = ErrorMessages.ID
INNER JOIN InterfaceErrors ON InterfaceErrors.ErrMessageID = ErrorMessages.ID
INNER JOIN UserGroups ON ErrorToNotify.UserGroupID  = UserGroups.ID;

answered Oct 6, 2015 at 10:07

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

Marko Radosevic Over a year ago

Yes, correct. But I need to loop over groups because I have different select for each group, this was just example. Tnx for help.

Collectives™ on Stack Overflow

T-SQL cursor, variable is not visible inside loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related