Sample Header Ad - 728x90

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