There may be times when you need to write a query and you need to make certain parts of that query dynamic, meaning the query might not be exactly the same every time you run it. SQL Server has a great tool we can use to create these dynamic queries.
In this very brief tutorial, we’ll discuss a very useful tool we can use when querying and developing SQL Server databases: Dynamic SQL. We’ll discuss these topics:
- What is dynamic SQL?
- Simple examples of dynamic SQL
- The power of dynamic SQL
- Tips, tricks, and links
Let’s get into it:
1. What is dynamic SQL?
Dynamic SQL allows us to save a SQL DML or DDL statement into a string variable, then use the SP_EXECUTESQL system stored procedure to execute the code outlined in that string variable.
DML stands for Data Manipulation Language. It’s the specific SQL language we use to query or manipulate data, like the SELECT, UPDATE or INSERT statements.
DDL stands for Data Definition Language. It’s the specific SQL language we use to create or change objects in a database, like CREATE, ALTER, or DROP.
Perhaps there is a query we want to run somewhat frequently, but the query might not be exactly the same every single time. Maybe there are parts to the query that depend on user input, for example. In that case, you can’t hard-code the query because you don’t know beforehand what the user is going to enter. This is where dynamic SQL comes in handy.
2. Simple examples of dynamic SQL
We’ll look at a very simple example of dynamic SQL. Let’s first create two simple tables to do some testing with. We’ll create a Products table:
CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL,
LastModified DATETIME
)
--Add some rows
INSERT Products (ProductName, Price, LastModified)
VALUES
('Large Bench', 198.00, '2021-06-22 00:00:00.000'),
('Small Bench', 169.40, '2021-07-08 00:00:00.000'),
('Coffee Table', 220.00, '2021-09-03 00:00:00.000'),
('Side Tables', 265.20, '2021-10-15 00:00:00.000'),
('Coat Rack', 45.00, '2021-12-21 00:00:00.000')
Then we’ll create an Customers table:
CREATE TABLE Customers
(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL,
LastUpdated DATETIME
)
--Add some rows
INSERT Customers (FirstName, LastName, LastUpdated)
VALUES
('Joshua', 'Porter', '4/1/2021'),
('Andrew', 'Bluefield', '6/13/2021'),
('Jack', 'Donovan', '6/30/2021'),
('Cindy', 'Thatcher', '7/9/2021'),
('Gordon', 'Acres', '8/1/2021'),
('Gretchen', 'Hamilton', '9/18/2021')
Let’s do a quick query against the Products table:
Now let’s talk about how we can run this same query using dynamic SQL. Let’s write this query into a string variable:
Then we can pass this string variable to the handy SP_EXECUTESQL system stored procedure:
You need to understand a thing or two about stored procedures. The SP_EXECUTESQL system stored procedure has a parameter called @stmt, as you can see in the last screenshot. The value we pass to this parameter is the string or string variable which contains a query that we want to have executed.
If we run this code, we see that it does indeed execute the query saved as our @productQuery string:
If we change the table outlined in the string variable, the result set will obviously be different:
Easy peasy!
3. The power of dynamic SQL
The examples above were simple examples to help you understand how dynamic SQL works. However, they don’t exactly capture the power of dynamic SQL. Those queries really didn’t have anything dynamic about them.
Let’s think about a couple of queries we might want to write. Let’s say we wanted to know the details of the entity that was most recently changed.
In our case, an “entity” can be either a Product or a Customer.
To display the details for the Product that was most recently change, we could write a query like this:
SELECT TOP(1) * FROM Products ORDER BY LastModified DESC
To display the details for the Customer that was most recently changed, we could write a very similar query:
SELECT TOP(1) * FROM Customers ORDER BY LastUpdated DESC
If we think about it, these two queries are nearly identical. The only difference is the table we’re querying and the column outlined in the ORDER BY clause. If we wanted to run these queries often, we could use dynamic SQL to basically plug in the appropriate values depending on which result set we want to see.
For example, here’s the code we would write if we wanted to see the details for the Customer who was most recently changed:
DECLARE @theTable NVARCHAR(10) DECLARE @lastModColName NVARCHAR(20) SET @theTable = 'Customers' SET @lastModColName = 'LastUpdated' DECLARE @theString NVARCHAR(70) SET @theString = 'SELECT TOP(1) * FROM ' + @theTable + ' ORDER BY ' + @lastModColName + ' DESC' EXECUTE SP_EXECUTESQL @stmt = @theString
This gives us the following result set:
If we wanted to run our query against the Products table instead, all we need to do is change the values in our @theTable and @lastModColName variables:
DECLARE @theTable NVARCHAR(10) DECLARE @lastModColName NVARCHAR(20) SET @theTable = 'Products' SET @lastModColName = 'LastModified' DECLARE @theString NVARCHAR(70) SET @theString = 'SELECT TOP(1) * FROM ' + @theTable + ' ORDER BY ' + @lastModColName + ' DESC' EXECUTE SP_EXECUTESQL @stmt = @theString
This gives us the following result set:
Again folks, all we had to do was change the values in those variables to give us a completely different query.
Truthfully, I think the easiest thing to do would be to put this code into a stored procedure:
CREATE PROCEDURE GetMostRecentRow @theTable NVARCHAR(10), @lastModColName NVARCHAR(20) AS DECLARE @theString NVARCHAR(70) SET @theString = 'SELECT TOP(1) * FROM ' + @theTable + ' ORDER BY ' + @lastModColName + ' DESC' EXECUTE SP_EXECUTESQL @stmt = @theString GO
Then we can call this simple stored procedure when we want to run the query, passing the appropriate values for @theTable and @lastModColName:
Just change the values to run against the Products table instead:
So simple!
4. Tips, tricks, and links
There’s only one tip I can share about dynamic SQL:
Tip # 1: The data type of your string variable needs to be a Unicode data type
You may have notice that the data type I’ve been using for the string variable is NVARCHAR. The value you use for the @stmt parameter of the SP_EXECUTESQL procedure needs to be a Unicode data type. So that’s either NTEXT, NCHAR or NVARCHAR.
Not sure what the difference is between NCHAR and NVARCHAR? Find out here:
SQL Server character data types (and the differences between them)
Links
There is a great book called T-SQL querying that you should get your hands on. It contains absolutely everything you need to know about querying SQL Server databases, including everything about dynamic SQL. You won’t regret owning this book, trust me. I reference it all the time. Get it today!
Next Steps:
Leave a comment if you found this tutorial helpful!
I normally see and use dynamic SQL within the definition of stored procedures. If you need a rundown on stored procedures and how they are used in SQL Server, definitely check out this tutorial:
SQL Server Stored Procedures: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
Thx