SQL Server + Dynamic Query 'Invalid Column Name'

Question

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.

Answer 1

Since cat is a varchar you need to include single quotes around it and you need to place the closing parentheses for the IN clause inside of the sql string.

The new code will be:

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)

See a SQL Fiddle Demo with the query string printed. This generates a query string like this:

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') -- cat surrounded in single quotes

Answer 2

You need to pass it as a string to the dynamic query

 SET @MemberNames = '''cat'''

Difference in the resulted query is

WHERE u.UserName IN (cat) -- cat is taking as a column name here
WHERE u.UserName IN ('cat') -- cat is taking as a string here

Answer 3

Your string:

WHERE u.UserName IN (' + @MemberNames + ')

will evaluate to:

WHERE u.UserName IN (cat)

because the apostrophes you have are just encapsulating the string, and there are no extra apostrophes around the string literal.

You need:

WHERE u.UserName IN (''' + @MemberNames + ''')

Alternately, you can leave your query as is, and seperate each ID with apostrophes in your @MemberNames variable:

SET @MemberName = '''cat'''           -- for a single user
SET @MemberName = '''cat'', ''dog'''  -- for multiple users