The sp_executesql stored procedure is used to execute dynamic SQL queries in SQL Server. A dynamic SQL query is a query in string format. There are several scenarios where you have an SQL query in the form of a string.
For example, if a user wants to search for a product by name, he will enter the name of the product in a search box on the website. The product name, which is in the form of a string will be concatenated with a SELECT query to form another string. These types of queries need to be executed dynamically because different users will search for different product names and so a query will need to be generated dynamically depending on the product name.
Now that you understand what dynamic SQL is, let’s see how the sp_executesql stored procedure can be used to execute dynamic SQL queries.
Let’s first create some dummy data that we can use to execute the examples in this article.
Creating dummy data
The following script creates a dummy database named BookStore with one table i.e. Books. The Books table has four columns: id, name, category, and price:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE Database BookStore; GO USE BookStore; CREATE TABLE Books ( id INT, name VARCHAR(50) NOT NULL, category VARCHAR(50) NOT NULL, price INT NOT NULL ) |
Let’s now add some dummy records in the Books table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE BookStore INSERT INTO Books VALUES (1, 'Book1', 'Cat1', 1800), (2, 'Book2', 'Cat2', 1500), (3, 'Book3', 'Cat3', 2000), (4, 'Book4', 'Cat4', 1300), (5, 'Book5', 'Cat5', 1500), (6, 'Book6', 'Cat6', 5000), (7, 'Book7', 'Cat7', 8000), (8, 'Book8', 'Cat8', 5000), (9, 'Book9', 'Cat9', 5400), (10, 'Book10', 'Cat10', 3200) |
The above script adds 10 dummy records in the Books table.
Working with the sp_executesql stored procedure
As I mentioned earlier, the sp_executesql stored procedure is used to execute dynamic SQL queries that are in the form of a string. Let’s see this in action.
Run the following script:
|
1 2 3 |
DECLARE @SQL_QUERY NVARCHAR(128) SET @SQL_QUERY = N'SELECT id, name, price FROM Books WHERE price > 4000 ' EXECUTE sp_executesql @SQL_QUERY |
In the script above, we declare a variable @SQL_QUERY and initialize it with a string query that returns the id, name, and price from the Books table where the price is greater than 4,000.
Next, we execute the sp_executesql stored procedure via the EXECUTE command. To execute a dynamic SQL query that is in the string format, you simply have to pass the string containing the query to the sp_executesql query.
It is important to mention that the string should be in the Unicode format before the sp_executesql stored procedure executes it. This is the reason we put ‘N’ at the beginning of the string containing the @SQL_QUERY variable. The ‘N’ converts the query string into the Unicode string format. Here is the output of the above script:
In real life database queries, the filter or condition is passed by the users. For instance, a user may search books within a specific search limit. In that case, the SELECT query remains the same, only the WHERE condition is changed. It is convenient to store the WHERE clause in a separate string variable and then concatenate the SELECT condition with the WHERE clause to create the final query. This is shown in the following example:
|
1 2 3 4 5 |
DECLARE @CONDITION NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) SET @CONDITION = 'WHERE price > 5000' SET @SQL_QUERY =N'SELECT id, name, price FROM Books '+ @CONDITION EXECUTE sp_executesql @SQL_QUERY |
Here in the script above, we declare two variables: @CONDITION and @SQL_QUERY. The @CONDITION variable contains the WHERE clause in string format whereas the @SQL_QUERY contains the SELECT query. Next, these two variables are concatenated and passed to the sp_executesql stored procedure. Here is the output:
The output shows all the books where the price is greater than 5,000.
Passing parameters to sp_executesql stored procedure
You can also pass parameters to the sp_executesql stored procedure. This is particularly handy when you don’t know the values used to filter records before runtime. To execute a sp_executesql stored procedure with parameters, you need to perform the following steps:
- First, you need to create a variable that is going to store the list of parameters
- Next, in the query string, you need to pass the names of the parameters
- Finally, you need to pass the query, the variable that contains a list of parameters and the actual parameters along with their values to the sp_executesql stored procedure
Look at the following example:
|
1 2 3 4 5 6 7 |
DECLARE @CONDITION NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) DECLARE @PARAMS NVARCHAR (1000) SET @CONDITION = 'WHERE price > @LowerPrice AND price < @HigherPrice' SET @SQL_QUERY = N'SELECT id, name, price FROM Books '+ @CONDITION SET @PARAMS = '@LowerPrice INT, @HigherPrice INT' EXECUTE sp_executesql @SQL_QUERY ,@PARAMS, @LowerPrice = 3000, @HigherPrice = 6000 |
In the script above, we create three variables: @CONDITION, @SQL_QUERY, and @PARAMS. The @PARAMS variable is a variable that stores the list of parameters that we will use in the string query format.
If you look at the value of the @CONDITION variable, it contains a WHERE clause with two parameters: @LowerPrice and @HigherPrice. To specify a parameter inside a string query, you simply have to prefix with the ‘@’ operator before the name of the parameter. Here the @LowerPrice parameter is used to set the lower bound for the price of books whereas the @HigherPrice sets the higher bound for the value in the price column of the BookStore table.
Next, while executing the sp_executesql stored procedure, the @SQL_QUERY variable which contains the string query is passed along with the @PARAMS variable which contains the parameter list. The parameter names i.e. @LowerPrice and @HigherPrice are also passed to the sp_executesql stored procedure along with the values 3,000 and 6,000 respectively. In the output, you will see the records where the price is between 3,000 and 6,000 as shown below:
Conclusion
This article explains the functionality of sp_executesql stored procedure which is used to execute dynamic SQL queries. The article shows how to execute the SELECT query in the form of a string via sp_executesql stored procedure. You also saw how to pass parameters to the sp_executesql stored procedure in order to execute queries where values are passed at runtime.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021