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)
| MESSAGES |
| SELECT * FROM MgmtTraining |
0 rows |
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)
| ClassID | ClassName | ClassDurationHours | ApprovedDate | |
| 1 | 1 | Embracing Diversity | 12 | 2007-01-01… |
| 2 | 2 | Interviewing | 6 | 2007-01-15… |
| 3 | 3 | Difficult Negotiations | 30 | 2008-02-12… |
| 4 | 4 | Empowering Others | 18 | 2012-08-01… |
| 5 | 8 | Passing Certifications | 13 | 2012-08-01… |
| 6 | 9 | Effective Communications | 35 | 2012-08-01… |
|
|
|
| 7 rows |
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')
| Messages |
| Msg 102, Level 15, State 3, Line 4 |
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'
| EmpID | LastName | FirstName | HireDate | LocationID | ManagerID | Status | |
| 1 | 11 | Smith | Sally | 1989-04-01… | 1 | NULL | Active |
| 2 | 14 | Smith | Janis | 2009-10-18… | 1 | 4 | Active |
|
|
|
|
|
|
| 2 rows |
Book On Amazon | Book On Flipkart Kit on Amazon | Kit on Flipkart
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hello Sir, I have a query in my mind, what i would like to ask. Sir can we execute a procedure with sql statement.
Like
Exec Proc_Name (select param1,param2,param3….. from table_name)
Hopefully u will help me out..
Thank you
Hi,
Nice one,
declare @x xml
and haveing data in xml form
I am building dynamic query like
set @strsql = ‘select SiteID = Node.Data.value(‘(SiteID)[1]’ , ‘int’) from @x.nodes(”/Root”) Node(Data)’
Giveing me truble like Must declare the scalar variable “@x”.
Any one know how to overcome from this?
Thanks
i have a dynamic query which throws error as “Unknown error” in catch? Its actually a error due to insertion of NULL value into NOT NULL column however the same error message is not displayed in Catch. Please let me know the reason for the same. Cant we do error handling for dynamic queries?
ALTER FUNCTION [dbo].[UNIQUE_COLUMN_VALUE] (@var_pool_key INT,
@var_in_table_name VARCHAR,
@var_in_field_name VARCHAR)
RETURNS NVARCHAR(25)
AS
BEGIN
DECLARE @return_value_argument NVARCHAR(25)
DECLARE @sql VARCHAR(250)
SET @sql = ‘SELECT ‘ + @return_value_argument + ‘= MAX(@var_in_field_name) + 1 FROM ‘ + @var_in_table_name + ‘AS A WHERE A.POOL_KEY = ‘ + CONVERT(VARCHAR,@var_pool_key)
EXECUTE @SQL
if (@return_value_argument = NULL)
begin
SET @return_value_argument = 1
end
RETURN @return_value_argument
END
——————–Execute function
SELECT [CCMS].[dbo].[UNIQUE_COLUMN_VALUE] (
1625
,’INSTRUMENT_10001′
,’SESSION_ROW_KEY’)
GO
Error
======
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.
———————————————————————————
How to dynamic sql query execute in function any other solution?
Is it possible to delete dbo and sys user from sql server 2005 .
iam using name location and panno i get empid.but in that name r location or pan no not there how to get….my condition is SELECT EmpId FROM Employees WHERE Name=@Name and PanNo=@PanNo and Location=@Location.if empid=1 thenupdate else insert
declare @SQL varchar(max)
declare @returnvalueArgument nvarchar(5)
declare @TableName varchar(max)
set @TableName = ‘Author’ –Where “Author” is correct table
SET @SQL = ‘SELECT ‘ + @returnvalueArgument + ‘= 1 FROM sysobjects where name = ‘ + @TableName + ‘ and xtype = ”U”’
Execute(@SQL)
if @returnvalueArgument is not null
(
select 1
)
ELSE
(
select 2
)
–even though correct table it show 2, it should show 1