What Happens When You Don’t Parameterize Dynamic SQL In SQL Server?

Unskinny Top

When I blogged about passing parameters to TOP, we ran into a problem that has many possible solutions.

Perhaps the least complex answer was just to fix the index. Nine times outta ten, that’s what I’d do.

Since I offered some other possible solutions, I do wanna talk about the pros and cons of them.

In this post, I’ll talk about using slightly less than fully parameterized dynamic SQL, which will, of course, terrify even people who live in Baltimore.


While I’m not smart enough to figure out a SQL injection method without altering the stored procedure, that doesn’t mean it can’t happen.

It might be more difficult, but not impossible. Here’s our prize:

CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT)


SET @sql = @sql + N'
    SELECT   TOP (' + RTRIM(@top) + ')
    FROM     dbo.Votes AS v
    WHERE    v.VoteTypeId = @i_vtid
    ORDER BY v.CreationDate DESC;

    PRINT @sql;
    EXEC sys.sp_executesql @sql, N'@i_vtid INT', @i_vtid = @vtid


If we fully parameterize this, we’ll end up with the same problem we had before with plan reuse.

Since we don’t, we can can use a trick that works on filtered indexes.

But There’s Another Downside

Every different top will get a new plan. The upside is that plans with the same top may get reused, so it’s got a little something over recompile there.

So if I execute these:

/*Run me*/
EXEC dbo.top_sniffer @top = 1, @vtid = 4;

/*Run me*/
EXEC dbo.top_sniffer @top = 5000, @vtid = 4;

They each get their own plan:

SQL Server Query Plan

And of course, their own plan cache entry.

SQL Server Query Results
I am your neighbor.

If lots of people look for lots of different TOPs (which you could cut down on by limiting the values your app will take, like via a dropdown), you can end up with a lot of plans kicking around.

Would I Do This?

Likely not, because of the potential risk, and the potential impact on the plan cache, but I thought it was interesting enough to follow up on.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

3 thoughts on “What Happens When You Don’t Parameterize Dynamic SQL In SQL Server?

  1. Why not parameterize @top for sp_executesql? That will avoid multiple plans.


    SET @sql = N’SELECT TOP (@top) * from sys.objects;’;

    EXEC sp_executesql @sql, N’@top INT’, @top = 2;

    1. Stan — make sure you read the post.

      I want different plans because not all values for TOP should share the same plan.


Comments are closed.