SQL cannot redeclare variable within cursor with multiple batches in procedure

I am using Microsoft's Server Management Studio 2012 for this, along with their SQL Server 2008. I am attempting to take a role (CustomerRole) and get all the users in that role, put the users into a table variable (@Roles) and use the MemberName "column" to store their logins. Ultimately I will be using this information to drop the users, their logins and eventually the role completely.

CREATE PROC dropLogins AS
DECLARE @Roles AS Table(DbRole varchar(20), MemberName varchar(50), MemberSID varchar(50))
INSERT INTO @Roles
EXEC sp_HelpRoleMember CustomerRole
DECLARE Member_Cursor CURSOR
DYNAMIC
FOR
SELECT DISTINCT MemberName
FROM @Roles
EXEC sp_HelpRoleMember CustomerRole OPEN Member_Cursor FETCH NEXT FROM Member_Cursor INTO @Roles --NOT DECLARED* WHILE @@FETCH_STATUS = 0 ALTER ROLE CustomerRole DROP MEMBER MemberName FETCH NEXT FROM Member_Cursor INTO @Roles --NOT DECLARED*
CLOSE Member_Cursor
DEALLOCATE Member_Cursor

The problem occurs at the areas that state --NOT DECLARED*. I receive the "Must declare the scalar variable "@Roles"." error in those 2 regions, though I'm not sure how I can get around this error. I understand that the error is most likely caused by the code being split up into 2 and 3 batches, though I'm unsure of how to prevent this. Any help is very much appreciated. Thank you!

1 Answer

You are creating a table variable called @roles.

Within the cursor, you return a field, I assume is varchar(). You cannot take the output of the cursor (a varchar field) and assign it into a table variable, they are different types

You could do

DECLARE @theRole VARCHAR(200)
FETCH NEXT FROM Member_Cursor into @theRole
INSERT INTO @roles (dbrole) VALUES (@theRole)

Or something similar