Hadi Fadlallah
Executing a batch of SQL commands

Working with parameters in the sp_executesql stored procedure

April 16, 2021 by

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.

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:

Executing a simple SQL query

Figure 1 – First query result

This query can also be executed as follows:

using sp_executesql stored procedure to execute a static SQLcommand

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:

using sp_executesql stored procedure to execute a parameterized SQL command

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:

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:

using sp_executesql stored procedure to execute a dynamic 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:

Let us say that we can generate the select queries that return the first row of each table using the following SQL command:

Generating select statements dynamically

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.

Executing a batch of SQL commands

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:

But this time, we need to filter those values for the first and Last names filtered based on two parameters: @FirstName and @Lastname.

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.

Passing input parameters to a dynamic SQL query

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:

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.

Using an output parameter within a dynamic SQL query

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.

Hadi Fadlallah
Latest posts by Hadi Fadlallah (see all)
SQL commands, Stored procedures, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a 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

18,461 Views