Dyanamic SQL to call sproc with input params and an output param

Question

Post reply

Dyanamic SQL to call sproc with input params and an output param

GBeezy

SSCarpal Tunnel

Points: 4723

December 30, 2023 at 6:32 pm

#4354563

All,

I have the following which works perfectly:

DECLARE @ProcessName VARCHAR(100) = 'ETL_InitialLoad'
,@TableName SYSNAME = 'dbo.tableA'
,@id NVARCHAR(30)


IF NOT EXISTS (SELECT * FROM dbo.ETL_Log WHERE ProcessName = @ProcessName AND TableName = @TableName AND ProcessEndTime IS NULL)
BEGIN
EXEC [dbo].[ETL_InsertLog] @ProcessName = @ProcessName, @TableName = @TableName, @id = @id OUTPUT;
END

SELECT @id

But, I need to use this in dynamic SQL as I will eventually be looping over a control table and @TableName will change with each iteration. So I am attempting the following:

DECLARE @ProcessName VARCHAR(100) = 'ETL_InitialLoad'
,@DestinationTableName SYSNAME = 'dbo.tableA'
,@id NVARCHAR(30)
,@SQL NVARCHAR(MAX)

SELECT @SQL = 'IF NOT EXISTS (SELECT * FROM dbo.ETL_lOG WHERE ProcessName = ''' + @ProcessName + ''' AND TableName = ''' + @DestinationTableName + '''  AND ProcessEndTime IS NULL)
BEGIN
EXEC [dbo].[ETL_InsertLog] @ProcessName = ''' + @ProcessName + ''' ,@TableName = ''' + @DestinationTableName + ''' ,@id = ''' + @id + '''OUTPUT'';''
END


'
PRINT (@SQL)

The result of this is nothing prints. If I get rid of the ,@id = ''' + @id + '''OUTPUT'';'' then it will print.

I've been googling and have seen examples with sp_executesql commands where I should pass in all the variables, but I am not able to follow these unfortunately especially when they @sql param is not calling a sproc.

Any and all help is greatly appreciated!

This topic was modified 11 months, 3 weeks ago by GBeezy.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

When you declare @id, its initial value is NULL. When you concat NULL to any other string, the default CONCAT_NULL_YIELDS_NULL connection setting will result in a NULL string.

Initialize @id with a value of an empty string, and your code returns data:

DECLARE @ProcessName VARCHAR(100) = 'ETL_InitialLoad'
,@DestinationTableName SYSNAME = 'dbo.tableA'
,@id NVARCHAR(30) = '' -- <-- note the initialized value ('')
,@SQL NVARCHAR(MAX)

Eddie Wuerch
MCM: SQL

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

I think you would want something more like this:

DECLARE @DestinationTableName SYSNAME = 'dbo.tableA'
,@id NVARCHAR(30)
,@ProcessName VARCHAR(100) = 'ETL_InitialLoad'
,@SQL NVARCHAR(MAX)

SELECT @SQL = 'IF NOT EXISTS (SELECT * FROM dbo.ETL_lOG WHERE ProcessName = ''' + @ProcessName + ''' AND TableName = ''' + @DestinationTableName + '''  AND ProcessEndTime IS NULL)
BEGIN
EXEC [dbo].[ETL_InsertLog] @ProcessName = ''' + @ProcessName + ''' ,@TableName = ''' + @DestinationTableName + ''' ,@id = @id OUTPUT;
END'

EXEC sys.sp_executesql @SQL, N'@id nvarchar(30) OUTPUT', @id = @id OUTPUT

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".