If you can anticipate typical values, run your stored procedure with such values whenever you (re-)create it. An execution plan will be created based on these values, and stored for future use. Most of my stored procedure .sql files end with an EXEC command with reasonable values, for exactly this purpose (that, and to identify basic errors in the code).
Of course this won't help if your stored procedure is very long running, enough that you can't reasonably execute it during production hours. Note that it's not enough to start the job, it has to complete successfully for a plan to be stored.
The line from MSDN is talking about using EXEC(), like this:
SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);
In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of certain parameters - in which case that will generate a different plan).
If you use sp_executesql then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):
SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sys.sp_executesql @sql, N'@x varchar(32)', @x;
As a bonus, this has built-in protection against dynamic SQL, and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here and please read up on SQL injection here and here.
If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloads and simple/forced parameterization. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:
The gist is: don't be afraid to use sp_executesql, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.
Best Answer
No, SQL Server will still see the reference as a parameter, which may be sniffed.
Generally, the plan for the whole procedure is compiled (and parameters sniffed) before execution begins, so your
SETwill 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