Not Entirely Parameterized Dynamic SQL

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:

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:

They each get their own plan:

Shellac

And of course, their own plan cache entry.

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!

3 thoughts on “Not Entirely Parameterized Dynamic SQL”

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

    DECLARE @sql NVARCHAR(MAX);

    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.

      Thanks,
      Erik

Leave a Reply

Your email address will not be published. Required fields are marked *