Last week I talked about single use plans. One way to increase execution plan re-use is to parameterize queries by wrapping them in sp_executesql.
I’ve started using this stored procedure enough that I wanted to write a short guide on converting a query into sp_executesql.
Our query before sp_executesql
SELECT CreationDate
FROM Users
WHERE DisplayName = N'Community'
AND Location = N'on the server farm'So there’s two values in the WHERE clause that could change between queries, if we want to look for a different DisplayName or Location. Let’s parameterize those in sp_executesql.
EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @LocationParam'That’s a good start, but now we need to tell the server what our parameters are. Add a comma, and add those after the first string like this:
EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @Location',
N'@DisplayNameParam NVARCHAR(40), @LocationParam NVARCHAR(100)'Completing the example of sp_executesql
And finally, we need another two commas. This is the part where we add the values for the parameters, in the same order that we declared them. That means DisplayNameParam first, then LocationParam second.
EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @Location',
N'@DisplayNameParam NVARCHAR(40), @Location NVARCHAR(100)',
N'Community', N'on the server farm'Perfect! Here’s the execution plan and the parameter list from the SELECT operator.
Changing the values to prove the execution plan will be re-used
Let’s look for a user that doesn’t exist. Here’s some parameters that won’t match any rows in StackOverflow2010.
EXEC sp_executesql N'
SELECT CreationDate
FROM Users
WHERE DisplayName = @DisplayNameParam
AND Location = @LocationParam',
N'@DisplayNameParam NVARCHAR(40), @LocationParam NVARCHAR(100)',
N'Not a real user', N'No location'Here’s the same execution plan and parameter list. The compiled values are Community and on the server farm, showing that the first plan was re-used.
That’s it for an introduction to sp_executesql. You can add more parameters, I just chose to use two for this demo.
Stay tuned!