动态SQL-在SET SQL字符串中使用声明的VARCHAR

Question

如何在SQL字符串中使用声明的变量@CodeID？当我运行下面的语句时；无效的对象名称(..("；错误

WHILE @FolderID <= @FolderMaxID
BEGIN
SELECT @Db  = Db 
FROM #Folders 
WHERE ID =  @FolderID
SET @Sql = N'
DECLARE @CodeID NVARCHAR(256)
SELECT TOP(1)  @CodeID=CodeType
FROM ' + @Db + '.bla.Field 
WHERE Name= ''Example''
SELECT DISTINCT C.Name
FROM ' + @Db + '.Document 
INNER JOIN ' + @Db + '.bla.Code_@CodeID  C  ON D.ID = C.ID'

EXEC ( @Sql )
SET @FolderID = @FolderID + 1
END

Answer 1

在我看来，您需要两个级别的动态SQL，第一级插入数据库名称(来自#文件夹(，第二级插入构造的表名称(基于数据库本地bla.Field表的CodeType列(。

我不知道如何使用sp_executesql参数化数据库名称或表名称，所以我坚持使用构建动态SQL和EXEC((。(如果有人提出在不使用参数时更喜欢sp_executesql而不是EXEC，那么切换可能是值得的。(

试试类似的东西：

WHILE @FolderID <= @FolderMaxID
BEGIN
SELECT @Db  = Db 
FROM #Folders 
WHERE ID =  @FolderID
SET @Sql = N'
DECLARE @CodeID NVARCHAR(256)
SELECT TOP(1)  @CodeID=CodeType
FROM ' + QUOTENAME(@Db) + '.bla.Field 
WHERE Name= ''Example''
DECLARE @Sql2 NVARCHAR(MAX) = N''
SELECT DISTINCT C.Name
FROM ' + QUOTENAME(@Db) + '.bla.Document D
INNER JOIN ' + QUOTENAME(@Db) + '.bla.'' + QUOTENAME(''Code_'' + @CodeID) + '' C  ON D.ID = C.ID
''
EXEC @sql2
'

EXEC ( @Sql )
SET @FolderID = @FolderID + 1
END

这在动态SQL中实现了动态SQL。外部sql模板中的双引号在内部sql中变为单引号。最初发布的代码似乎缺少Document表的模式限定符和别名，所以我插入了它们("bla"one_answers"D"(。我还按照Larnu的建议，在注入的名称周围添加了QUOTENAME。

动态sql的第一个级别将生成类似于以下内容的内容：

SELECT TOP(1)  @CodeID=CodeType
FROM [db1].bla.Field 
WHERE Name= 'Example'
DECLARE @Sql2 NVARCHAR(MAX) = N'
SELECT DISTINCT C.Name
FROM [db1].bla.Document D
INNER JOIN [db1].bla.' + QUOTENAME('Code_' + @CodeID) + ' C  ON D.ID = C.ID
'
EXEC @sql2

第二个层次会生成类似的东西：

SELECT DISTINCT C.Name
FROM [db1].bla.Document D
INNER JOIN [db1].bla.[Code_Table1] C  ON D.ID = C.ID

请注意，每个循环迭代都将生成一个单独的结果。如果希望组合结果，则需要定义一个#temp表，将各个结果插入该表，然后在脚本末尾选择组合结果。

请注意，我还没有测试过上面的特定代码，所以如果它不能直接运行，可能需要一些调试(在EXEC之前添加"PRINT@sql2"(。

附录

根据下面的@trenton-ftw注释，out参数可以用于捕获第一个查询的结果，这样它就可以包含在第二个查询中，而不需要嵌套。仍然需要执行两次死刑。下面是一个修改后的示例。

DECLARE @Folders TABLE (ID INT IDENTITY(1,1), Db sysname)
INSERT @Folders VALUES ('db1'), ('db2')
DECLARE @SearchName NVARCHAR(256) = 'Example' 
DECLARE @Db sysname
DECLARE @Sql NVARCHAR(MAX)
DECLARE @CodeID NVARCHAR(256)
DECLARE @FolderMaxID INT = (SELECT MAX(ID) FROM @Folders)
DECLARE @FolderID INT = 1
WHILE @FolderID <= @FolderMaxID
BEGIN
SELECT @Db = Db 
FROM @Folders 
WHERE ID = @FolderID
SET @Sql = N'
SET @CodeID = @SearchName + ''-Test''
--SELECT TOP(1) @CodeID = CodeType
--FROM ' + QUOTENAME(@Db) + '.bla.Field 
--WHERE Name = @SearchName'
PRINT @Sql
EXEC sp_executesql @Sql,
N'@SearchName NVARCHAR(256), @CodeID NVARCHAR(256) OUTPUT',
@SearchName, @CodeID OUTPUT
SET @Sql = N'
--SELECT DISTINCT C.Name
--FROM ' + QUOTENAME(@Db) + '.bla.Document D
--    INNER JOIN ' + QUOTENAME(@Db) + '.bla.' + QUOTENAME('Code_' + @CodeID) + ' C  ON D.ID = C.ID'
PRINT @Sql
EXEC sp_executesql @sql
SET @FolderID = @FolderID + 1
END

出于演示的目的，我还将搜索名称参数化为输入参数，并添加了一些临时代码，使其可以独立测试。最终版本将取消对实际sql的注释，并删除print语句和test@CodeIDassignemnt。