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.

Disclaimer

---

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)
AS
BEGIN

DECLARE @sql NVARCHAR(MAX) = N'';

SET @sql = @sql + N'
    SELECT   TOP (' + RTRIM(@top) + ')
             v.Id, 
             v.PostId, 
             v.UserId, 
             v.BountyAmount, 
             v.VoteTypeId, 
             v.CreationDate
    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

END;

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:

And of course, their own plan cache entry.

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 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.