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
