Monday, March 16, 2009

SQL Server Error: Dynamic SQL and 'you must declare scalar variable @whatever'

A good CF-er friend of mine says blogging about mistakes makes me look bad to potential employers. I think that it might help someone out there save some time, and to remind myself that I certainly don't know as much as I think I do!

So here's a fun one for today.

I had to modify an MSSQL stored procedure to pass an already defined parameter to another proc. That inner proc helps build dynamic SQL. So I [roughly] modified the WHERE clause setup of the query like so:

SET @dSQL = @dSQL + ' AND PersonId = @PersonId'

Drove me crazy trying to find out why it kept telling me to declare the variable. After about 20 minutes of digging, I realized my mistake.

When the dynamic SQL runs, @PersonId is no longer defined! Seems obvious now that I mention it, but, well...you had to be there. The correct syntax is:

SET @dSQL = @dSQL + ' AND PersonId = ' + @PersonId

Again...it might seem obvious now that you're reading it, but trust me...it wasn't at all obvious when I was trying to debug it.

- Will Belden
March 16, 2009