Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.
Variables
Variables are extremely useful in SQL scripts. They offer the flexibility needed to create powerful tools for yourself. They are especially useful in dynamic SQL environments. They can, and are often used as counters for loops to control how many times you want the code inside the loop to run. A statement can use it to test the value of the data, or a stored procedure can use it to return a value.
We can use the DECLARE statement to declare one or more variables. From there we can utilize the SET command to initialize or assign a value to the variable. Here is a quick example:
|
1 2 3 |
DECLARE @MAXRECORD INT |
You can declare multiple variables in the same DECLARE statement just separate them with a comma. Example would be:
|
1 2 3 4 5 6 7 8 |
/*Declare Variables*/ DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) |
That seems simple enough.
We have declared a variable called @maxrecord as an INT data type. Now we can use the SET operator to assign it a value. Like this:
|
1 2 3 |
SET @MAXRECORD = 10 |
We just assigned the INT variable @maxrecord with a value of 10. This can change throughout a SQL script, thus providing great flexibility.
Think of a variable as a box that acts as a place holder for whatever value you want to put in it.
The SET statement can only be used on variable at a time. This can become cumbersome if you need to assign values to multiple attributes. But you do need to use multiple set statements.
|
1 2 3 4 5 |
SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' |
However you can use a select statement to make this process a little easier. (Largely depending on what you’re trying to accomplish). Here is a quick example of assigning values to variables in a select statement.
|
1 2 3 4 5 |
SELECT @DBNAME = '['+[DBName]+']' FROM [#DBRecovery] WHERE [ROWNUM] = @CURRENTRECORD |
Dynamic SQL
I often think of Dynamic SQL as “code that thinks”. Although it’s not quite cognitive, it can be used to handle a lot of tasks that would likely be repetitive or have a high administrative overhead. The concept of Dynamic SQL is one that allows you to build a SQL statement or a full batch of SQL code and present it as a character string. Then you can take this string and execute it. SQL Server gives us a couple of options to execute character strings as T-SQL Code. One is via the simple EXECUTE command (I like to use “EXEC”) or the sp_executesql stored procedure.
So, again why would we ever want to use Dynamic SQL? One reason would be the automation of administrative tasks. Let’s say you have a non-production server with every limited space and you want to make sure that all the databases on your server are in simple recovery mode (to minimize the growth of the transaction logs). Rather than manually query the server to identify the databases that meet this parameter and then manually set them to the proper recovery model you can write a piece of code to execute periodically. This code can identify the databases that aren’t compliant with your set rules and make then make the change for you. Thus reducing your administrative overhead. This will save you time and allow you to focus on meeting real deadlines, projects and such.
Understanding the EXEC Command
The EXEC command was around before the more robust sp_executesql stored procedure. Rather than placing Unicode strings into one of the parameters, you simply place it in parenthesis after exec.
EXEC (sqlstatement)
You can even place the piece of code into a variable and put the variable in parenthesis. (Note that EXEC allows the use of both regular character strings and Unicode character strings as input).
Understanding the sp_executesql Stored Procedure
This is a great built-in stored procedure for SQL Server. It allows you to use input and output parameters allowing your dynamic SQL code to be secure and efficient. The Parameters not only serve a purpose for flexibility, but they also inhibit SQL Injection attacks since they appear as operands and not part of the actual code.
Also note that as long as the SQL statement itself remains the same, yet only the parameters change, the query optimizer will utilize the same cached execution plan.
Let’s use the example above regarding databases and simple recovery model. I’m not going to write out the entire script for you here as it is beyond the scope of the article. But the snippets I provide should provide enough information to show how to use this method.
My goal is to find out if any databases are not in the simple recovery model. If they are, I want them to be put into the compliant mode.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
/*Insert Databases names into Temp Table*/ BEGIN TRY DROP TABLE #DBRecovery END TRY BEGIN CATCH SELECT 1 END CATCH SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]), DBName = [name], RecoveryModel = [recovery_model_desc] INTO #DBRecovery FROM sys.[databases] WHERE [recovery_model_desc] NOT IN ('Simple') /*Declare Variables*/ DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) /*Initialize Variables*/ SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' /*BEGIN LOOP*/ WHILE @CURRENTRECORD <= @MAXRECORD BEGIN SELECT @DBNAME = '['+[DBName]+']' FROM [#DBRecovery] WHERE [ROWNUM] = @CURRENTRECORD /*Build Command*/ SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE' EXEC sys.[sp_executesql] @SQL /*Next Record*/ SET @CURRENTRECORD = @CURRENTRECORD + 1 END DROP TABLE [#DBRecovery] |
I like to take the criteria and place the databases that need to be altered into a temp table.
|
1 2 3 4 5 6 7 8 |
SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]), DBName = [name], RecoveryModel = [recovery_model_desc] INTO #DBRecovery FROM sys.[databases] WHERE [recovery_model_desc] NOT IN ('Simple') |
As you can see, I am using variables as they play an important role in creating dynamic SQL.
|
1 2 3 4 5 6 7 |
DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) |
I use SET to assign values to a few of the variables. In this case, I want the @maxrecord variable to know how many records in total we must loop through. The @currentrecord acts as our counter. Once @currentrecord is equal the @maxrecord the loop will end.
|
1 2 3 4 5 6 |
/*Initialize Variables*/ SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' |
I use the @SQL variable to store the actual code that will execute. In this case, it is a simple command to change the recovery model of the database. Remember we already identified which databases are not in simple mode when we created the temp table.
|
1 2 3 4 |
/*Build Command*/ SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE' |
Then finally, I use the sp_executesql stored procedure to execute the command that is stored in the @SQL variable.
|
1 2 3 |
EXEC sys.[sp_executesql] @SQL |
As the loop moves onto the next record in the table the @DBNAME will store the name of a different database, thus having the command execute on a different database. This is an example of SQL code acting dynamically.
Another way to visualize a loop:
He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs.
SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years.
View all posts by Aamir Syed
- Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment - July 27, 2018
- Use cases for Query Store in SQL Server - July 18, 2018
- When to Use SQL Temp Tables vs. Table Variables - February 21, 2017