I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because they need to perform a calculation on one of the parameters that get passed in.
In nearly all cases, you’re better off… not doing that.
Here are a couple examples of why.
Passing in a function like GETDATE, and even passing it to another function like DATEADD, the optimizer can interpret them to their current values and make a pretty decent guess based on them.
This is a Good Enough™ guess.
Once you assign that function to a value, everything gets awkward.
That’s a Pretty Bad™ guess, but not the end of the world. What’s bad is that the guess never changes even if you change the span of time.
Look what happens if we just add one day instead of one year.
We get the exact same guess as before — 821,584 rows. Bummer.
It’s nearly the same thing in stored procedures. What I see more often is people there is people passing in one parameter for a start date, and then using a local variable to figure out an end date.
CREATE OR ALTER PROCEDURE dbo.dangit_bobby ( @start_date datetime ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; DECLARE @later datetime = DATEADD(DAY, 1, GETDATE()); SELECT records = COUNT_BIG(*) FROM dbo.express_yourself AS ey WHERE ey.some_date >= @start_date AND ey.some_date < @later; END; GO EXEC dbo.dangit_bobby @start_date = '2021-05-19'; GO
Let’s change how we use the parameter, and put it into some date math in the where clause instead.
CREATE OR ALTER PROCEDURE dbo.dangit_bobby ( @start_date datetime ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; SELECT records = COUNT_BIG(*) FROM dbo.express_yourself AS ey WHERE ey.some_date >= @start_date AND ey.some_date < DATEADD(DAY, 1, @start_date); END; GO EXEC dbo.dangit_bobby @start_date = '2021-05-19'; GO
We get a Much Closer™ estimate. What a lovely day.
It’s often tempting to take shortcuts like this, especially if you need to reuse the same calculation multiple times.
Now, look, if you stare closely enough at these plans, you’ll notice that they all finish quickly enough. That’s because I have a small table with a wonderful index on the column I care about.
The point here is to show you how bad estimates can turn up even in ideal circumstances when you do things The Wrong Way™.
As long as you’re not wrapping columns in functions like this, you’re probably okay.
Thanks for reading!