Does changing a parameter value in a stored procedure before the query affect the cardinality estimate? I routinely &quot;scrub&quot; 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.
Does it count as using a local variable if the value originated from a parameter?

No, SQL Server will still see the reference as a parameter, which may be sniffed.

&quot;Parameter sniffing&quot; refers to a process whereby SQL Server's
execution environment &quot;sniffs&quot; the current parameter values during
compilation or recompilation, and passes it along to the query
optimizer so that they can be used to generate potentially faster
query execution plans. The word &quot;current&quot; refers to the parameter
values present in the statement call that caused a compilation or a
recompilation. Both in SQL Server 2000 and SQL Server 2005, parameter
values are sniffed during compilation or recompilation for the
following types of batches:

Stored procedures
Queries submitted via sp_executesql
Prepared queries


Generally, the plan for the whole procedure is compiled (and parameters sniffed) before execution begins, so your SET will not have executed when the sniffing occurs. An exception may occur if the statement is recompiled for any reason after the procedure starts executing. In that case, the recompilation process will see the new value you have set.
In most cases, each statement referencing a parameter will be optimized based on the sniffed value, not any changes you have made within the procedure.
Further reading: Parameter Sniffing, Embedding, and the RECOMPILE Options