Not So Forced Parameterization

Asking The Wrong Question

Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do.

There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find.

Now, there’s nothing in that list that says what I’m about to show you won’t work, but it’s kind of inferred here.

Statements that reference variables, such as WHERE T.col2 >= @bb.

When you’ve finished rolling your eyes around the known universe, keep reading.

This Also Goes For Assigning Variables

So, if you’re the kind of nutso-wacko that wants their variables to have values, hold onto your Librium!

Let’s check it out.

This sets FP on.

Now I’m gonna run two queries. One of them selects a count, and the other assigns that count to a variable.

The first query is parameterized just fine.

See those little parameters? They used to be literals.

How nice for you.

The second query doesn’t fare so well. 

No. Not at all. One may even call it unfair.

Ribbit.

Workarounds?

If you have queries that do this, and you want them to benefit from parameterization, one workaround is to insert the value you would assign your variable to into a temp table, like this.

This query will get parameterized.

Nifty.

Then you can just hit that temp table for the value.

Not bad.

Not great if you do it a lot, but hey.

Thanks for reading!

Previous Post
[Video] Office Hours 2018/11/21 (With Transcriptions)
Next Post
Getting Sneaky With Forced Parameterization

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}