Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?
5
votes
2
answers
2576
views
I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this:
-- Scrub params
SET @SearchText = NULLIF(@SearchText, '')
SET @ThreadId = NULLIF(@ThreadId, 0)
SET @Hashtag = NULLIF(@Hashtag, '')
But then from this article I read the following:
> If the query predicate uses a local variable, consider rewriting the
> query to use a parameter instead of a local variable. The value of a
> local variable is not known when the Query Optimizer creates the query
> execution plan. When a query uses a parameter, the Query Optimizer
> uses the cardinality estimate for the first actual parameter value
> that is passed to the stored procedure.
Does it count as using a local variable if the value originated from a parameter? I'm wondering if my parameter scrubbing could affect the creation of the query execution plan.
Asked by adam0101
(163 rep)
Sep 2, 2020, 03:41 PM
Last activity: Sep 2, 2020, 05:42 PM
Last activity: Sep 2, 2020, 05:42 PM