Wsrtjtyk

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.

LAB Instructions:

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

My Code:

-- 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.

Try this one, it might need some changes depending on the database context it needs to run in. I added a temporary table to create some records. You can generate the statements without using a cursor however. If you need to directly execute them, then you will need a cursor.

DECLARE  @Administrators TABLE(firstname varchar(255),

                               lastname varchar(255))



INSERT INTO @Administrators VALUES('John','Wick'),

                                  ('Frodo','Baggins'),

                                  ('Gandalf','The Grey')



-- Debug for printing or executing. Printing = 1 , Executing = 0 or NULL

DECLARE @debug bit = 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  LOCAL FAST_FORWARD 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



    --(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;

    -- ,CHECK_POLICY  = off means that it will not use the password policy check

        SET @DynamicSQL = 'CREATE LOGIN ' +QUOTENAME(@FirstName+@LastName)+' with password = ''temp'' ,CHECK_POLICY  = off';

        IF @debug =1 

        BEGIN

        print (@DynamicSQL);

        END

        ELSE

        BEGIN

        exec(@DynamicSQL);

        END



        --(3) set the default database for the login to the MyGuitarShop database;

        SET @DynamicSQL = 'ALTER LOGIN '+QUOTENAME(@FirstName+@LastName)+'WITH DEFAULT_DATABASE = [MyGuitarShop]';

        IF @debug =1 

        BEGIN

        print (@DynamicSQL);

        END

        ELSE

        BEGIN

        exec(@DynamicSQL);

        END



        --(4) create a user for the login with the same name as the login; and

        SET @DynamicSQL = 'CREATE USER '+QUOTENAME(@FirstName+@LastName)+' FOR LOGIN '+QUOTENAME(@FirstName+@LastName)+';'

        IF @debug =1 

        BEGIN

        print (@DynamicSQL)

        END

        ELSE

        BEGIN

        exec(@DynamicSQL)

        END



        --(4) assign the user to the OrderEntry role you created in Exercise 1

        SET @DynamicSQL = 'ALTER ROLE OrderEntry ADD MEMBER '+QUOTENAME(@FirstName+@LastName)+';'

        IF @debug =1 

        BEGIN

        print (@DynamicSQL)

        END

        ELSE

        BEGIN

        exec(@DynamicSQL)

        END





     -- 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?

I understand that the exercise is to use a cursor and dynamic SQL, but just to give you another view (and something to take beyond this one class I suppose), you can do a lot of administrative tasks like this without cursors. I feel that when you default to cursors for tasks where you do need to process things iteratively in a loop, you tend to then rely on them for tasks where you don't need a loop at all. So almost always better to think about what you need to do to a set of rows, rather than to each row. In this case, we need to execute the same set of commands against a set of users. Borrowing from the other answer:

DECLARE  @Administrators TABLE(firstname varchar(255),

                               lastname varchar(255));



INSERT INTO @Administrators VALUES('John','Wick'),

                                  ('Frodo','Baggins'),

                                  ('Gandalf','The Grey');



DECLARE @sql nvarchar(max) = N'';



;WITH u AS (SELECT u = QUOTENAME(firstname + lastname) FROM @Administrators)

SELECT @sql += CHAR(13) + CHAR(10)

  + N'CREATE LOGIN ' + u + N' WITH PASSWORD = ''temp'', CHECK_POLICY = OFF;

       ALTER LOGIN ' + u + N' WITH DEFAULT_DATABASE = MyGuitarShop;

       CREATE USER ' + u + N' FROM LOGIN ' + u + N';

       ALTER ROLE  OrderEntry ADD MEMBER ' + u + N';'

FROM u;



PRINT @sql;

--EXEC MyGuitarShop.sys.sp_executesql @sql;

Result:

CREATE LOGIN [JohnWick] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;

  ALTER LOGIN [JohnWick] WITH DEFAULT_DATABASE = MyGuitarShop;

  CREATE USER [JohnWick] FROM LOGIN [JohnWick];

  ALTER ROLE OrderEntry ADD MEMBER [JohnWick];

CREATE LOGIN [FrodoBaggins] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;

  ALTER LOGIN [FrodoBaggins] WITH DEFAULT_DATABASE = MyGuitarShop;

  CREATE USER [FrodoBaggins] FROM LOGIN [FrodoBaggins];

  ALTER ROLE OrderEntry ADD MEMBER [FrodoBaggins];

CREATE LOGIN [GandalfThe Grey] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;

  ALTER LOGIN [GandalfThe Grey] WITH DEFAULT_DATABASE = MyGuitarShop;

  CREATE USER [GandalfThe Grey] FROM LOGIN [GandalfThe Grey];

  ALTER ROLE OrderEntry ADD MEMBER [GandalfThe Grey];