Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?

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.

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" 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 "current" 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, note any changes you have made within the procedure.