Execute a stored procedure in another database with params

Question

I need to get the column names of a table that's located in another database. The following script works for the active database but I need to run it against another database in the same server instance:

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

Here's the issue... the database isn't known at compile time. Its passed into a stored procedure at runtime. So I see no alternative but to use dynamic sql. In the past I've tried using Use [DBName] in a dynamic sql script but always ran into problems until I realized I could do this:

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

But I'm having difficulty figuring out how to do this with the script I mentioned above. My first attempt looked like:

-- @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

But when it runs it doesn't interpret @ColumnList as a valid variable. What am I missing?

Solution 1

I'm not sure where you picked up that syntax, but here is how I would do it:

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

Solution 2

You need to remove the = signs from the @params argument and add OUT or OUTPUT after the @ColumnList argument.

Correct:

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

Incorrect:

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

Comments

[Kenneth Cochran]

Which syntax are you referring to?

[Adir D]

EXEC @sp_executesql @sql - I assume you meant to be calling EXEC sp_executesql @sql in some way.

[Kenneth Cochran]

EXEC @sp_executesql @sql uses nested dynamic sql to call sp_executesql against @DatabaseName. Picked it up from here

[Adir D]

Just very weird to call the variable @sp_executesql and use it exactly in a place where the stored procedure sp_executesql would be expected. Nested dynamic SQL doesn't seem to be required here... did you try my code?

[Kenneth Cochran]

Yea, I noticed that when I looked at Aaron's solution