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.

Taryn · Accepted Answer · 2013-02-26T20:16:54.873

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

thank you bluefeet. it worked. i cant vote yet cos i need 50 points — Lolade Ahmed 'banji, May 10 '17 at 08:56

LittleBobbyTables - Au Revoir · Answer 2 · 2013-02-26T20:24:11.157

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

Kaf · Answer 3 · 2013-02-26T20:15:29.600

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

edited Feb 26 '13 at 20:15

answered Feb 26 '13 at 20:10

Kaf

33,101
7
58
78

score 0 · Answer 4 · answered Feb 26 '13 at 20:10

Your dynamic query uses ' characters as the string delimiter, so the last line ends up reading like this after the string is built:

WHERE u.UserName IN (cat)

According to this, cat reads like a column name.

To fix it, you need to include escaped ' characters in either the definition of

`SET @MemberNames = '''cat'''`

or in the string being used to build the sql:

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

SQL Server + Dynamic Query 'Invalid Column Name'

4 Answers4

Linked