Mixing
Homework help. T-SQL Server 2016: dynamic SQL and cursor for class lab
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
(Lab Instructions and my code are posted at end of body).
Hello,
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.
t-sql sql-server-2016 dynamic-sql logins cursors
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
(Lab Instructions and my code are posted at end of body).
Hello,
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.
t-sql sql-server-2016 dynamic-sql logins cursors
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
(Lab Instructions and my code are posted at end of body).
Hello,
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.
t-sql sql-server-2016 dynamic-sql logins cursors
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
(Lab Instructions and my code are posted at end of body).
Hello,
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.
t-sql sql-server-2016 dynamic-sql logins cursors
t-sql sql-server-2016 dynamic-sql logins cursors
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
ZMU
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
ZMU
82
asked 2 hours ago
ZMU
82
82
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
ZMU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago
add a comment |Â
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
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?
answered 2 hours ago
Randi Vertongen
4588
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also useQUOTENAME()for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).
– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
add a comment |Â
up vote
2
down vote
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];
answered 2 hours ago
Aaron Bertrand♦
148k18279475
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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?
answered 2 hours ago
Randi Vertongen
4588
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also useQUOTENAME()for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).
– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
add a comment |Â
up vote
1
down vote
accepted
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?
answered 2 hours ago
Randi Vertongen
4588
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also useQUOTENAME()for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).
– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
add a comment |Â
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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?
answered 2 hours ago
Randi Vertongen
4588
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?
answered 2 hours ago
Randi Vertongen
4588
edited 1 hour ago
answered 2 hours ago
Randi Vertongen
4588
answered 2 hours ago
Randi Vertongen
4588
answered 2 hours ago
Randi Vertongen
4588
4588
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also useQUOTENAME()for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).
– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
add a comment |Â
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also useQUOTENAME()for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).
– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also use
QUOTENAME() for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).– Aaron Bertrand♦
1 hour ago
Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also use
QUOTENAME() for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon).– Aaron Bertrand♦
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
You are completely right, I edited it, thanks!
– Randi Vertongen
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again!
– ZMU
1 hour ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers.
– Randi Vertongen
42 mins ago
1
1
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
I'll check out all of your suggestions. Thank you!
– ZMU
35 mins ago
add a comment |Â
up vote
2
down vote
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];
answered 2 hours ago
Aaron Bertrand♦
148k18279475
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
add a comment |Â
up vote
2
down vote
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];
answered 2 hours ago
Aaron Bertrand♦
148k18279475
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
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];
answered 2 hours ago
Aaron Bertrand♦
148k18279475
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];
answered 2 hours ago
Aaron Bertrand♦
148k18279475
answered 2 hours ago
Aaron Bertrand♦
148k18279475
answered 2 hours ago
Aaron Bertrand♦
148k18279475
answered 2 hours ago
Aaron Bertrand♦
148k18279475
148k18279475
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
add a comment |Â
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks!
– ZMU
52 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example.
– ZMU
31 mins ago
add a comment |Â
ZMU is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
ZMU is a new contributor. Be nice, and check out our Code of Conduct.
ZMU is a new contributor. Be nice, and check out our Code of Conduct.
ZMU is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221994%2fhomework-help-t-sql-server-2016-dynamic-sql-and-cursor-for-class-lab%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
I'm trying to resist the urge to answer in a comment, but in the spirit of being helpful... I recommend first debugging your dynamic sql; and second attempting to execute your dynamic statement in a cursor. Try first reading through the demos here. Once you understand how to properly form dynamic sql, I imagine googling the error message will get you the rest of the way 😊
– Peter Vandivier
2 hours ago
Thanks for the link. I bookmarked it and will be reading it soon.
– ZMU
1 hour ago