I ran into a situation today where I was generating dynamic SQL that appeared to be valid. I could take the generated SQL strings and execute them with no issues, but when executed dynamically, I got the error message “Must declare the scalar variable “@someVar”, even though the variable in question was declared properly. I learned that the problem occurred because the variables were declared outside of the scope of the dynamic SQL that I was executing. I was pointed to the following article: http://support.microsoft.com/kb/262499. This article describes how to use input and output parameters with the sp_executesql stored procedure. Another good article on dynamic SQL parameters can be found on the SimpleTalk web site here.
SQL Server AlwaysOn Revealed