SQL Server +动态查询“无效的列名”

小编典典

SQL Server +动态查询“无效的列名”

sql

我正在尝试执行以下动态查询，但出现错误：无效的列名’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)

如果我将其更改为普通查询，则可以：

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')

任何人都可以指出我的错误吗？谢谢。

阅读 173

2021-04-22

共1个答案

小编典典

由于cat是varchar，因此需要在其周围包含单引号，并且需要IN在sql字符串内放置该子句的右括号。

新的代码将是：

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)

查看打印了查询字符串的SQL
Fiddle演示。这将生成一个查询字符串，如下所示：

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

2021-04-22