Dynamic SQL unknown column name

[jimspicer]

Hi,
We are using common table structures with different names so Dynamic SQL may work for us. But I am having a problem with this query:

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT UsersData_ID FROM ' + @Tablename + ' WHERE Users = ' + @UserName

exec sp_executeSql @SQL

The @UserName column contains email addresses. When I run this query I get this error...

The multi-part identifier "jim@perpetualbudget.com" could not be bound.

or I get this error...

Invalid column name 'jim@spicer'

It seems the query engine doesn't like the @ sign but what can I do about it? All of our other dynamic SQL queries are working. Thanks for any help, Jim Spicer

[wisemx]

In previous SQL Server versions this was a bug but it has been addressed and most of the time if this error is seen now it is due to the scope of the query.
Look at this Blog post where adding a "order by" clause resolved it:
http://aartemiou.blogspot.com/2009/01/multi-part-identifier-could-not-be.html

[jimspicer]

Hi wisemx,
Thanks for the reply. I tried reducing the Scope of the query by adding parameters and the ORDER BY but still getting the same errors. Have tried changing the name of the field, but the error stays the same. This is a show-stopper for us. The same query in static SQL works fine.

[wisemx]

Thanks for the feedback.
Try posting that in the Microsoft SQL Server forums you should get some good tips:
http://social.msdn.microsoft.com/Forums/en/category/sqlserver

[jimspicer]

Hi again,
We're on SQL Server 2005...did this get fixed in 2008?

TIA,
Jim

[wisemx]

Possibly, I'm not sure but there are tons of fixes in it.
You also get more here with 2008 and R2 so it is worth upgrading.

[jimspicer]

Hi,
I asked Tech Support to look at my code also, and they suggested I try this:

SET @SQL = 'SELECT UsersData_ID FROM ' + @Tablename + ' WHERE Users = ' + quotename(@Users,'''') + ' AND blah blah

The quotes are four single quotes. I had read this in the literature but didn't know to apply it in this situation. Thanks for the help Jorge and Wise.

[wisemx]

So you got it to work? If so, good job ;-)