使用 dynamicSQL 將數據從表行傳遞到創建登錄語句

我有一個實驗室練習，其中涉及創建一個腳本，該腳本使用動態 SQL 和一個游標來循環遍歷表的每一行，然後創建一個 loginID 等等。

我們沒有做任何關於游標/動態 SQL 的工作，因為我們的講師說我們永遠不需要它。我認為游標內的操作查詢不會有任何問題，但我一直堅持如何使用動態SQL 字元串將表行中的數據傳遞到游標內的 CREATE LOGIN 語句中。

游標是否應該嵌套在 DynamicSQL 程式碼中？反之亦然？我沒有嘗試執行它，因為它還沒有完成，但我想我還是走錯了路。任何人都可以幫忙嗎？謝謝。

這是到目前為止的實驗室說明和我的（不完整的）程式碼。

實驗室說明：

編寫一個腳本，使用動態 SQL 和游標循環訪問 Administrators 表的每一行，然後

(1) 為每一行創建一個登錄 ID，該 ID 由管理員的名字和姓氏組成，中間沒有空格；

(2) 為每次登錄設置一個臨時密碼“temp”；

（3）設置登錄MyGuitarShop數據庫的預設數據庫；

(4) 為登錄創建一個與登錄名同名的使用者；和

(4) 將使用者分配給您在練習 1 中創建的 OrderEntry 角色

我的程式碼：

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

感謝任何願意並能夠提供幫助的人。

試試這個，它可能需要根據它需要執行的數據庫上下文進行一些更改。我添加了一個臨時表來創建一些記錄。但是，您可以在不使用游標的情況下生成語句。如果您需要直接執行它們，那麼您將需要一個游標。

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?

引用自：https://dba.stackexchange.com/questions/221994