SQL SERVER – Executing Dynamic SQL – SQL Queries 2012 Joes 2 Pros Volume 4 – Query Programming Objects for SQL Server 2012

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 4. 

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Why buy this Book: As you get further into SQL you will discover areas you like more than others. Maybe your good at queries or performance tuning. It all comes down to writing code but that code needs to be in the right place. Code can become a function, stored procedure, Trigger, Cursor, or a script. What type of code is right for the different SQL objects can how to handle errors is what this book is all about.

What will I learn after reading this book:  Constraints, Triggers, Views, Stored Procedure Techniques, Functions, Error Handling, Dynamic SQL, and Cursors.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 4 in the file SQLQueries2012Vo4Chapter14.0Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Exec Dynamic SQL

The words “I want to” make very little sense without context. However the words “I want to have lunch with you” give context and the query makes sense. Just as incomplete sentences don’t make sense in spoken languages, neither do partial SQL statements. The following statement is a complete statement for SQL to understand:

SELECT * FROM MgmtTraining

That previous statement will return the seven records from the MgmtTraining table. Before we dive into Dynamic SQL let’s look at a simple SELECT Statement. If we select these two lines of code one at a time, the query does not run. SQL does not understand incomplete statements. This statement has two lines and we need both of these lines in order to run:

SELECT *

Will not run by itself and neither will:

FROM MgmtTraining

There is a big difference in the next two statements:

SELECT * FROM MgmtTraining ‘SELECT * FROM MgmtTraining’

The first statement will run but the second will not. The single quotes around the second line make it a string and not a complete statement. If we could turn the string of the second statement into what it contains then we could execute the content as a SQL statement. One way to do this is by using the EXEC command. EXEC stands for execute and turns a string into running SQL code when the string is placed in a set of parentheses:

EXEC (‘SELECT * FROM MgmtTraining’)

Dynamic DML statements Many of us have used variables before to change how a predicate is run. For example LastName = @LastName might get a different result set from the Employee table when the value for @LastName changes. We can also use variables to change what table we are going to query from.

Beginning with a simple query let’s convert it to a Dynamic statement. Using the query from the first section of this chapter we will first declare the SELECT * and FROM MgmtTraining as @variables and set them equal to their corresponding lines of code. To see if we can reconstruct the query from variables we will add a print statement that concatenates the two variables into a message.

DECLARE @Select NVARCHAR(50) = ‘SELECT * ‘DECLARE @From NVARCHAR(50) = ‘FROM MgmtTraining’PRINT (@Select + @From)

This shows the same basic SELECT statement from the MgmtTraining table that we started with. The message is the same as the query. Our goal is to create Dynamic SQL, so simply change the PRINT command to EXEC. Instead of just printing a message SQL executes the (@Select + @From) string as a SQL statement.

DECLARE @Select NVARCHAR(50) = 'SELECT * ' DECLARE @From NVARCHAR(50) = 'FROM MgmtTraining' EXEC (@Select + @From)

Using sp_executesql

A straight forward observation would be that not all queries are as simple as SELECT * FROM Employee. Sometimes seeing every employee is too much data. We may want to see just the employees with a LastName of Brown, Adams, or Smith:

SELECT * FROM Employee WHERE LastName = 'Brown' SELECT * FROM Employee WHERE LastName = 'Adams' SELECT * FROM Employee WHERE LastName = 'Smith'

Hard coding the LastName into the criteria is not efficient coding when there are constantly changing variable values for LastName. It would better to use a variable for LastName and set that variable to be used by the query. To return all the employees with a last name of Smith we are not going to hard code ‘Smith’, we will use the variable @EmpLastName. We can then change the way this query runs just by changing the value of the variable. The following code shows our idea but is not yet complete and will not run:

DECLARE @SQL NVARCHAR(200) SET @SQL = 'SELECT * FROM Employee WHERE LastName = @EmpLastName' DECLARE @EmpLastName NVARCHAR(30) EXEC (@SQL, @EmpLastName = 'Smith')

Since there is the new variable @EmpLastName it would seem we should use a declare statement and set the value for use by the query. In this case we will call it @SQL as a NVARCHAR (30) data type. We will attempt to pass in the @EmpLastName value of Smith within the EXEC command. The following example shows the EXEC statement did not accept the variable value and resulted in an error message.

DECLARE @SQL NVARCHAR(200) SET @SQL = 'SELECT * FROM Employee WHERE LastName = @EmpLastName' DECLARE @EmpLastName NVARCHAR(30) EXEC (@SQL, @EmpLastName = 'Smith')

Incorrect syntax near ‘)’.

0 rows

The EXEC command can only execute a concatenated string and is not capable of swapping variable values. Our goal is to execute the Dynamic SQL, and swap out the @EmpLastName variable for a set value (such as ‘Smith’).

To swap out variables in real time, use the sp_executesql system stored procedure. Sp_executesql can swap out dynamic parameters right before execution. This code will execute the @SQL query and replace the @EmpLastName with Smith.

DECLARE @SQL NVARCHAR(200) SET @SQL = 'SELECT * FROM Employee WHERE LastName = @EmpLastName' EXEC sp_executesql @SQL, N'@EmpLastName NVARCHAR(50)', @EmpLastName = 'Smith'

Book On Amazon | Book On Flipkart Kit on Amazon | Kit on Flipkart

Reference: Pinal Dave (https://blog.sqlauthority.com)