This article explains how to use the sp_executesql system stored procedure to run static and dynamic SQL queries and how to pass input and output parameters while using it.
- Note: All examples in this article are executed under SQL Server 2019 using the AdventureWorks2017 database
What is the sp_executesql stored procedure?
A SQL Server built-in stored procedures used to run one or multiple SQL statements stored within a string. This stored procedure allows executing static or strings built dynamically.
Syntax
This stored procedure’s syntax is straightforward; you need to pass the SQL statement as a Unicode string or variable followed by the parameters if they exist.
|
1 |
sp_executesql N'statment', [ {parameters definitions}, {parameters values} ] |
Executing static SQL queries
As mentioned above, sp_executesql can be used to execute a static SQL statement. This statement can be passed directly as a Unicode string to the stored procedure or stored within a variable of type NVARCHAR.
As an example, let us assume that in the AdventureWorks database, let assume that we have a query that returns all employees from the Person table as following:
|
1 2 3 4 5 6 7 |
SELECT [BusinessEntityID] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person] WHERE [PersonType] = 'EM' |
Figure 1 – First query result
This query can also be executed as follows:
|
1 2 3 4 5 6 7 |
EXEC sp_executesql N'SELECT [BusinessEntityID] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person] WHERE [PersonType] = ''EM''' |
Figure 2 – Second query result
- Note that the N literal before the SQL command is to convert the string to a Unicode string
Another method to run this query is to store it within a variable of type NVARCHAR and pass it as a parameter:
|
1 2 3 4 5 6 7 8 |
DECLARE @SQL NVARCHAR(4000) = N'SELECT [BusinessEntityID] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person] WHERE [PersonType] = ''EM'''; EXEC sp_executesql @SQL |
Figure 3 – Third query result
As shown in this section, when executing a static SQL query, there is no benefit of using the sp_executesql stored procedure.
Executing dynamic SQL queries
The primary purpose of using the sp_executesql stored procedure is to run dynamic SQL queries. Dynamic SQL queries are those built at runtime based on one or more variable values.
For example, let us assume that we need to build a query that returns all employees by showing the columns with an NVARCHAR data type. To get the list of the NVARCHAR columns in the Person tables, we can use the following query:
|
1 2 |
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' and TABLE_NAME = 'Person'; |
To select only the NVARCHAR columns from the Person table, we should first concatenate the column names returned by the query above (we can use the STRING_AGG() function). Then we should build an SQL statement and execute it as follows:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @cols NVARCHAR(4000) = ''; DECLARE @SQL NVARCHAR(4000) = ''; SELECT @cols = STRING_AGG(COLUMN_NAME,',') FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' and TABLE_NAME = 'Person'; SELECT @SQL = 'SELECT ' + @cols + ' FROM Person.Person WHERE PersonType = ''EM'''; EXEC sp_executesql @SQL; |
Figure 4 – Dynamic SQL query example
- Note: Using dynamic SQL queries is not a good practice when the variables are used to store only the WHERE clause’s filtering values
Another example where we need to use a dynamic SQL query is if we need to query all tables created on the Person schema:
|
1 |
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Person' AND TABLE_TYPE = 'BASE TABLE' |
Let us say that we can generate the select queries that return the first row of each table using the following SQL command:
|
1 |
SELECT 'SELECT TOP 1 * FROM Person.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Person' AND TABLE_TYPE = 'BASE TABLE' |
Figure 5 – Generating queries using SQL command
To execute those queries, we must concatenate them into one SQL statement and pass them as a parameter to the sp_executesql stored procedure.
|
1 2 3 4 5 6 7 |
DECLARE @SQL NVARCHAR(MAX); SELECT @SQL = STRING_AGG('SELECT TOP 1 * FROM Person.' + TABLE_NAME ,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Person' AND TABLE_TYPE = 'BASE TABLE'; EXEC sp_executesql @SQL |
Figure 6 – Executing batch of SQL commands
Working with parameters
As mentioned in the first section, to execute a parameterized query, we should pass two parameters to the stored procedure; the first must contain all the parameters names and data types (definition). The second one should contain all values. This section provides two examples: passing input values to the executed SQL statement and storing an output value into an output parameter.
Passing an input parameter
Let’s assume that we need to reuse the query that selects only the NVARCHAR columns from the Person table:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @cols NVARCHAR(4000) = ''; DECLARE @SQL NVARCHAR(4000) = ''; SELECT @cols = STRING_AGG(COLUMN_NAME,',') FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' and TABLE_NAME = 'Person'; SELECT @SQL = 'SELECT ' + @cols + ' FROM Person.Person WHERE PersonType = ''EM'''; EXEC sp_executesql @SQL; |
But this time, we need to filter those values for the first and Last names filtered based on two parameters: @FirstName and @Lastname.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @cols NVARCHAR(4000) = ''; DECLARE @SQL NVARCHAR(4000) = ''; DECLARE @params NVARCHAR(4000) = '@Lastname NVARCHAR(255), @FirstName NVARCHAR(255)' SELECT @cols = STRING_AGG(COLUMN_NAME,',') FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' and TABLE_NAME = 'Person'; SELECT @SQL = 'SELECT ' + @cols + ' FROM Person.Person WHERE PersonType = ''EM'' AND LastName = @LastName AND FirstName = @FirstName '; EXEC sp_executesql @SQL, @params, @FirstName = 'Jossef', @Lastname = 'Goldberg'; |
In the first two lines, we defined the variables used to build the dynamic SQL statement as explained previously. In the third line, we created a variable that stores the definition of the parameters.
When executing the stored procedure, we pass the variable that stored the dynamic SQL parameters definition followed by those parameters’ values.
Figure 7 – Passing input parameters to a dynamic SQL query
Storing a value into an output parameter
Now, let’s assume that we need a SQL command where the user passes the table name as input and get the row count value into an output parameter.
The main difference with input parameters is that this time we need to add the OUTPUT keyword in the parameter definition, and we should define a variable outside the dynamic SQL to store the output value as following:
|
1 2 3 4 5 6 7 8 |
DECLARE @Tablename NVARCHAR(255) = 'Person.Person'; DECLARE @outCount BIGINT; DECLARE @params NVARCHAR(255) = '@Count BIGINT OUTPUT' DECLARE @SQL NVARCHAR(4000) = 'SELECT @Count = COUNT(*) FROM ' + @Tablename; EXEC sp_executeSQL @SQL, @params, @Count = @outCount OUTPUT; PRINT(@outCount) |
In this query, the @outCount variable defined outside of the dynamic SQL statement is used to read the value stored within the @Count variable defined within the dynamic SQL statement.
Figure 8 – Using an output parameter within a dynamic SQL query
Exec vs sp_executesql
The main difference between the EXEC or EXECUTE operators and the sp_executesql built-in stored procedure is that the EXEC operator is used to execute a stored procedure or a SQL command passed as a string or stored within a variable. Simultaneously, it cannot execute parameterized queries which means that it is more vulnerable for SQL injections.
Conclusion
This article explained the sp_executesql built-in stored procedure briefly in SQL Server and how to use it to execute dynamic SQL queries. Additionally, we learned how to work with input and output parameters.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023