如何将存储过程中EXEC输出的字符串返回至调用存储过程？

需求说明 #

现有存储过程GetLinkCriteria运行时，内部EXEC会输出一段拼接的条件字符串，需要将该字符串传递给调用它的存储过程，并存入调用方的变量中，该存储过程会在另一个存储过程的循环内被调用。

现有存储过程的问题 #

当前存储过程通过EXEC执行动态SQL，仅通过PRINT输出结果，无法将字符串返回给调用方；同时动态SQL中存在冗余的引号拼接，可简化。

修改方案 #

使用sp_executesql替代直接EXEC，通过输出参数将生成的条件字符串返回给调用方，同时优化字符串拼接逻辑，移除末尾多余的And。

修改后的存储过程代码 #

ALTER PROCEDURE [dbo].[GetLinkCriteria]
    @TableName nvarchar(max),
    @LinkCriteria nvarchar(max) OUTPUT -- 添加输出参数
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sql nvarchar(max);

    SET @sql = N'
        DECLARE @tempCriteria nvarchar(max);
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''JOBID'' )
            BEGIN SET @tempCriteria = ''target.JOBID = source.JOBID And '' END          
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''stackid'')
            BEGIN SET @tempCriteria += ''target.stackid = source.stackid And '' END     
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''testtypeid'')
            BEGIN SET @tempCriteria += ''target.testtypeid = source.testtypeid And '' END   
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''colid'')
            BEGIN SET @tempCriteria += ''target.colid = source.colid And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''runid'')
            BEGIN SET @tempCriteria += ''target.runid = source.runid And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''lineid'')
            BEGIN SET @tempCriteria += ''target.lineid = source.lineid And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''component'')
            BEGIN SET @tempCriteria += ''target.component = source.component And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''sampledparameter'')
            BEGIN SET @tempCriteria += ''target.sampledparameter = source.sampledparameter And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''parameter'')
            BEGIN SET @tempCriteria += ''target.parameter = source.parameter And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''filename'')
            BEGIN SET @tempCriteria += ''target.filename = source.filename And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''runtype'')
            BEGIN SET @tempCriteria += ''target.runtype = source.runtype And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''parentmetalparameter'')
            BEGIN SET @tempCriteria += ''target.parentmetalparameter = source.parentmetalparameter And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''metal'')
            BEGIN SET @tempCriteria += ''target.metal = source.metal And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''gassampled'')
            BEGIN SET @tempCriteria += ''target.gassampled = source.gassampled And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''sampledgas'')
            BEGIN SET @tempCriteria += ''target.sampledgas = source.sampledgas And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''purpose'')
            BEGIN SET @tempCriteria += ''target.purpose = source.purpose And '' END
        IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = ''isblank'')
            BEGIN SET @tempCriteria += ''target.isblank = source.isblank And '' END
        -- 移除末尾多余的" And "
        IF LEN(@tempCriteria) > 0
            SET @LinkCriteria = STUFF(@tempCriteria, LEN(@tempCriteria) - 3, 4, '''');
        ELSE
            SET @LinkCriteria = '''';
    ';

    -- 使用sp_executesql绑定参数，传递输入表名并获取输出结果
    EXEC sp_executesql 
        @sql,
        N'@TableName nvarchar(max), @LinkCriteria nvarchar(max) OUTPUT',
        @TableName = @TableName,
        @LinkCriteria = @LinkCriteria OUTPUT;
END

调用示例（在调用存储过程中） #

DECLARE @TableName nvarchar(max) = 'YourTargetTableName',
        @ResultCriteria nvarchar(max);

-- 调用存储过程获取条件字符串
EXEC [dbo].[GetLinkCriteria] @TableName, @ResultCriteria OUTPUT;

-- 使用获取到的条件字符串，例如拼接进其他SQL逻辑
PRINT @ResultCriteria;

关键说明 #

1. 输出参数传递：通过定义输出参数@LinkCriteria，并在sp_executesql中绑定该参数，实现动态生成字符串向调用方的传递。
2. 参数化防注入：使用sp_executesql替代直接字符串拼接，避免SQL注入风险，同时简化参数传递逻辑。
3. 格式优化：通过STUFF函数移除末尾多余的And，确保生成的条件格式符合后续使用要求。

内容的提问来源于stack exchange，提问作者Garry_G