I’m honestly not crazy about dynamic T-SQL within stored procedures. There are just a few too many opportunities to mess it up with dire circumstances to your server and your data. However, I absolutely recognize that dynamic T-SQL may be needed, and, in some situations, the best way to solve a problem. If you must use dynamic T-SQL, there are ways that are much more efficient than others. The very best thing you can do if you need to build dynamic strings to execute T-SQL in your stored procedures is use sp_executesql.

The main reason I advocate for sp_executesql is because you can build out completely dynamic strings of T-SQL, but, you can still take advantage of parameters. Parameters help you avoid a chat with the parents of Bobby Tables and can get you much more plan reuse. However, it’s worth noting that, because these are parameters, you’re going to have to deal with the good, and the bad, of parameter sniffing. Let’s see it at work.

Here’s a stored proc that uses sp_executesql (for no good reason, but this is just an example):

CREATE PROCEDURE dbo.DynamicAddressByCity ( @City NVARCHAR(30) )
AS 
DECLARE@TSQL NVARCHAR(MAX) ,
@Params NVARCHAR(MAX) ;
SET @TSQL = N'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.[Name] AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
        JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;'
SET @Params = N'@City NVARCHAR(30)';
EXECUTE sp_executesql @TSQL, @Params, @City = @City;

If I execute this query:

EXEC dbo.DynamicAddressByCity @City = N'London';

Then I’ll get an execution plan that looks like this:

If I were to re-execute the query using a different parameter:

EXEC dbo.DynamicAddressByCity @City = N'Mendon';

I’ll end up with the same execution plan. If we look at the properties for the SELECT operator:

We can see that the plan was compiled using a parameter value of ‘London’ and executed using a parameter value of  ’Mendon.’ I already hear people saying, “So what?” Well, let’s remove the query from cache. I’ll query the Dynamic Management Objects (DMO) to get the plan handle and then remove it from cache using FREEPROCCACHE with the plan handle. Now, we’ll re-execute the query but using the parameter value of ‘Mendon.’ Here is the resulting execution plan:

Yeah, different right. That’s because of the differences in the statistics between the two values. ‘London’ will return over four hundred rows while ‘Mendon’ will only return two. Those differences, accurately portrayed within the statistics for the column, result in different execution plans because the parameters were ‘sniffed’.

Parameter sniffing absolutely applies to sp_executesql. While you should be using those parameters, don’t lose sight of the fact that this could lead to bad parameter sniffing.

For lots more on query tuning, if you’re in the Dallas area, I’ll be putting on an all-day pre-conference seminar on Friday, November 1, 2013, before SQL Saturday 255. You can go here to sign up for the event. Hurry, the early bird offer is going to expire soon.