在另一个数据库中执行带参数的存储过程

Question

我需要获取位于另一个数据库中的表的列名。以下脚本适用于活动数据库，但我需要针对同一服务器实例中的另一个数据库运行它：

SELECT @ColumnList = 
    CASE
        WHEN @ColumnList IS NULL THEN name
        WHEN @ColumnList IS NOT NULL THEN @ColumnList + ',' + name
    END
FROM sys.columns
WHERE object_id = Object_Id(@TableName);

以下是问题所在...数据库在编译时未知，会在运行时传递给存储过程。因此，我认为没有别的选择，只能使用动态SQL。过去我曾尝试在动态SQL脚本中使用Use [DBName]，但总是遇到问题，直到我意识到我可以这样做：

SET @SQL = 'SELECT Foo FROM Bar'
SET @sp_executesql = quotename(@DatabaseName) + '..sp_executesql'
EXECUTE @sp_executesql @SQL

但是我在使用上述脚本时遇到了困难，不知道该如何实现。我的第一次尝试如下所示：

-- @DatabaseName and @TableName are parameters of the 
-- stored procedure containing this script

DECLARE @ColumnList nvarchar(max),
        @SQL nvarchar(max),
        @sp_executesql nvarchar(max) = quotename(@DatabaseName) + '..sp_executesql';
SET @SQL = 
'SELECT @ColumnList = 
    CASE
        WHEN @ColumnList IS NULL THEN name
        WHEN @ColumnList IS NOT NULL THEN @ColumnList + '','' + name
    END
FROM sys.columns
WHERE object_id = Object_Id(@TableName);'

EXECUTE @sp_executesql @SQL,
        N'@ColumnList = nvarchar(max) OUT, @TableName = sysname',
        @ColumnList, @TableName

但是当它运行时，它不将@ColumnList解释为有效的变量。我漏掉了什么？

- Kenneth Cochran

Answer 1

我不确定你从哪里学到了那种语法，但是这是我会如何去做：

-- I assume these are parameters, so declaring them separately:
DECLARE 
    @DatabaseName SYSNAME = N'db_name',
    @TableName SYSNAME = N'table_name';

DECLARE 
    @sql NVARCHAR(MAX), 
    @columnList NVARCHAR(MAX);

SELECT @sql = N'SELECT @ColumnList = COALESCE(@ColumnList + '','', '''') 
    + c.name
    FROM [$db$].sys.columns AS c
    INNER JOIN [$db$].sys.tables AS o
    ON c.[object_id] = o.[object_id]
    WHERE o.name = @TableName;';

SET @sql = REPLACE(@sql, '$db$', @DatabaseName);

EXEC sp_executesql @sql, 
    N'@ColumnList NVARCHAR(MAX) OUTPUT, @TableName SYSNAME',
    @ColumnList OUTPUT, @TableName;

SELECT @ColumnList

- Aaron Bertrand

---

你指的是哪种语法？ - Kenneth Cochran

我猜你想以某种方式调用 EXEC sp_executesql @sql，而不是 EXEC @sp_executesql @sql。 - Aaron Bertrand

EXEC @sp_executesql @sql 使用嵌套动态 SQL 对 @DatabaseName 调用 sp_executesql。这个方法来源于这里。 - Kenneth Cochran

在变量名为@sp_executesql的地方使用它，而且恰好在存储过程sp_executesql被期望的地方使用它，这很奇怪。嵌套动态SQL似乎在这里并不需要...你试过我的代码了吗？ - Aaron Bertrand

Answer 2

您需要从@params参数中删除=符号，并在@ColumnList参数后添加OUT或OUTPUT。

正确的写法：

EXECUTE @sp_executesql @SQL,
        N'@ColumnList nvarchar(max) OUT, @TableName sysname',
        @ColumnList OUT, @TableName

错误：

EXECUTE @sp_executesql @SQL,
        N'@ColumnList = nvarchar(max) OUT, @TableName = sysname',
        @ColumnList, @TableName

- Anthony Faull

---

是的，我注意到了当我看了Aaron的解决方案时。 - Kenneth Cochran