I am trying to execute the following dynamic query but I got an error: Invalid column name 'cat'
DECLARE @SQLDelQuery AS NVARCHAR(1200)
DECLARE @MemberNames varchar(50)
SET @MemberNames = 'cat'
SET @SQLDelQuery = 'SELECT [Email] FROM [aspnet_Membership] am
INNER JOIN [aspnet_Users] u
ON (am.UserId = u.UserId)
INNER JOIN [Member] m
ON (am.UserId = m.UserId)
WHERE u.UserName IN (' + @MemberNames + ')
EXECUTE(@SQLDelQuery)
If I change it to the normal query I am fine:
SELECT [Email] FROM [aspnet_Membership] am
INNER JOIN [aspnet_Users] u
ON (am.UserId = u.UserId)
INNER JOIN [Member] m
ON (am.UserId = m.UserId)
WHERE u.UserName IN ('cat')
Anyone can point out my error? Thanks.
Best Answer
Since
cat
is a varchar you need to include single quotes around it and you need to place the closing parentheses for theIN
clause inside of the sql string.The new code will be:
See a SQL Fiddle Demo with the query string printed. This generates a query string like this: