存储过程执行过程中的无效列名错误

Question

我无法执行存储过程。它正在抛出一个错误

无效列名“DW201401”

用于执行存储过程的命令：

exec RM_UTIL_MODE server,'DW201401'

存储过程代码：

ALTER Procedure [dbo].[RM_UTIL_MODE]
    @ServerName varchar(50),
    @Value varchar(50) 
As
Begin
   declare @query nvarchar(max) 

   set @query = N'SELECT mode FROM ' + @ServerName + 
                N'.master.dbo.sysdatabases WHERE name =' + @Value

   exec sp_executesql @query
End

但是，当我试图单独运行查询时，如下面所示，它会给出结果。

select mode, name 
from server.master.dbo.sysdatabases 
where name = 'DW201401'

Answer 1

据推测，这个问题是关于@Value的引号。

declare @query nvarchar(max) 
set @query = N'SELECT mode FROM ' 
+ @ServerName
+ N'.master.dbo.sysdatabases
WHERE name = '''+@Value+'''';

但是，我将使用参数替换：

declare @query nvarchar(max) ;

set @query = N'SELECT mode
FROM ' + @ServerName + N'.master.dbo.sysdatabases
WHERE name = @value';

exec sp_executesql @query, N'@value varchar(50)', @value = @value;

您已经在使用sp_executesql，所以您最好正确地使用它。注意:您不能替换服务器名。

编辑：

为了详细说明这个注释，我将以如下方式编写代码：

    declare @sql nvarchar(max) ;

    set @sql = N'
SELECT mode
FROM @ServerName.master.dbo.sysdatabases
WHERE name = @value';

    set @sql = replace(@sql, '@ServerName', quotename(@ServerName));

    exec sp_executesql @sql, N'@value varchar(50)', @value = @value;

当使用动态SQL时，我不再使用字符串连接将查询拼凑在一起。相反，我设置了持卡器并使用replace()。我发现连接很难维护，而且常常模糊了SQL正在做的事情。虽然使用replace()有更多的开销(而且我经常使用它多次)，但是它对于防止错误和维护代码是值得的(另外，我的查询通常运行一段时间，因此与查询时间相比，开销是最小的)。

Answer 2

您的选择看起来如下：

select mode, name from server.master.dbo.sysdatabases where name = DW201401

因此，您需要在动态查询中添加转义引号：

exec RM_UTIL_MODE cefmtqcfindv3,'DW201401'

ALTER Procedure [dbo].[RM_UTIL_MODE]
@ServerName varchar(50),@Value varchar(50) 
As

Begin

declare @query nvarchar(max) 
set @query = N'SELECT mode FROM ' 
+ @ServerName
+ N'.master.dbo.sysdatabases
WHERE name ='''+@Value+''''

exec sp_executesql @query

End

作为一个建议，当您构建动态sql时，尝试使用PRINT而不是EXEC，然后获取打印出来的内容并试用它。大多数情况下，你会知道哪里出了问题。

作为一个例子：

ALTER Procedure [dbo].[RM_UTIL_MODE]
@ServerName varchar(50),@Value varchar(50) 
As

Begin

declare @query nvarchar(max) 
set @query = N'SELECT mode FROM ' 
+ @ServerName
+ N'.master.dbo.sysdatabases
WHERE name ='''+@Value+''''

PRINT @query

--exec sp_executesql @query

End