I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.
We've done NO work concerning cursors/dynamicSQL because our instructor said we'll never need it. I don't think I'd have any problems with an action query within a cursor, but I'm stuck on how to pass data from a table row into a CREATE LOGIN statement, within a cursor, using a dynamicSQL string.
Should the cursor be nested inside the DynamicSQL code? Vice versa? I haven't attempted to run it as it's not finished, but I think I'm on the wrong track anyway. Can anyone help? Thanks.
Here are the lab instructions and my (incomplete) code so far.
Write a script that uses dynamic SQL and a cursor to loop through each row of the Administrators table and
(1) create a login ID for each row that consists of the administrator’s first and last name with no space between;
(2) set a temporary password of “temp” for each login;
(3) set the default database for the login to the MyGuitarShop database;
(4) create a user for the login with the same name as the login; and
(4) assign the user to the OrderEntry role you created in Exercise 1
-- Create Dynamic SQL DECLARE @DynamicSQL varchar (max) -- Cursor variables -- Should "SET @DynamicSQL =" go before the cursor variables? DECLARE @FirstName varchar(255), @LastName varchar(255), @updateCount int; -- needed? -- Create cursor, select table rows through which to loop DECLARE Admin_Cursor CURSOR FOR SELECT FirstName, LastName -- Data needed from rows? FROM Administrators -- Table with needed rows OPEN Admin_Cursor; -- Open the cursor -- Get values from first row and store in cursor variables (FirstName/LastName) FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName; -- set loop condition/begin loop WHILE (@@FETCH_STATUS = 0) BEGIN SET @DynamicSQL = (CREATE LOGIN 'FirstName' + 'LastName' -- this is where Im lost -- some code -- some code -- begin next iteration of loop FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName; END; CLOSE Admin_Cursor; -- Close the cursor DEALLOCATE Admin_Cursor; -- Erase from database? EXEC (@DynamicSQL); -- Execute the dynamicSQL string
Thanks to anyone willing and able to help.