Вернуть содержимое EXEC из хранимой процедуры

Как возврат значения вызывающей процедуре, так и отказ от SQL-инъекции сводятся к использованию выходных параметров как в хранимой процедуре, так и в sp_executesql. Попробуйте что-то вроде этого:

CREATE OR ALTER PROCEDURE [dbo].[GetLinkCriteria]
    @TableName nvarchar(max),
    @rv nvarchar(max) output
AS

/*
  declare @rv nvarchar(max)
  exec GetLinkCriteria @tablename='foo', @rv=@rv output
  print @rv
*/
BEGIN
    SET NOCOUNT ON
    DECLARE @sql nvarchar(max);

    SET @sql = '
            DECLARE @linkcriteria nvarchar(max) = '''';

            IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND COLUMN_NAME = ''JOBID'' )
                BEGIN SET @linkcriteria = ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''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 @linkcriteria += ''target.isblank = source.isblank And '' END
            --print(@linkcriteria)
            set @rv = @linkcriteria;
            ';
    --print @sql
    EXEC sp_executesql @sql, N'@tableName nvarchar(max), @rv nvarchar(max) output', @tableName=@tableName, @rv = @rv output
END

Я исправил это на основе того, что предоставил Дэвид. Мне просто нужно было установить возвращаемое значение «rv» в мою переменную @linkcriteria. и удалите оператор PRINT, который использовался для тестирования.

Например.

            SET @rv = @linkcriteria
            --PRINT @linkcriteria;
            ';
    EXEC sp_executesql @sql, N'@LinkTableName nvarchar(max), @rv nvarchar(max) output', @LinkTableName=@LinkTableName, @rv=@rv output