Return the contents of an EXEC from a stored procedure

Question

I have the following which returns a string produced by the EXEC in the output when running the stored procedure directly but I need to pass this string back to a calling stored procedure.

This stored procedure will be called from within the main loop of another stored procedure, and I need the text produced by the EXEC to be stored in a variable on the calling procedure.

I have used the EXEC sp_executesql command before but don't know if this applies here.

Output:

target.JOBID = source.JOBID And target.stackid = source.stackid And target.testtypeid = source.testtypeid And target.sampledparameter = source.sampledparameter And 

Completion time: 2024-06-26T16:42:29.4295301+01:00

This is the stored procedure I need to return the output of the EXEC:

ALTER PROCEDURE [dbo].[GetLinkCriteria]
    @TableName nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sql nvarchar(max);

    SET @sql = '
            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;
            ';
    EXEC ('DECLARE @linkcriteria nvarchar(max);' + @Sql)
END

Answer 1

Both returning the value to the calling procedure and avoiding SQL injection come down to using output parameters, both on the stored procedure and on sp_executesql. Try something like this:

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