Dynamic SQL - must declare scalar variable

19. November 2008 by Bennett.

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.

Posted in T-SQL | Print | No Comments »