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)
DECLARE @sql NVARCHAR(MAX) = N'';
SET @sql = @sql + N'
SELECT TOP (' + RTRIM(@top) + ')
FROM dbo.Votes AS v
WHERE v.VoteTypeId = @i_vtid
ORDER BY v.CreationDate DESC;
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:
EXEC dbo.top_sniffer @top = 1, @vtid = 4;
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!