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