Call a Stored Procedure or Function from Dynamic SQL - with Nullable Parameter

Question

I am trying to call an sql function accepting a nullable parameter - from a dynamic SQL statement.

Creating the dynamic statement is difficult because when the parameter value is 'NULL' the concatentation causes the whole statement to be empty. I have the following:

SET dynamicQuery = 
   'select * from [qlik].udf_getStatistic( ''' + @myParameter + ''' )'

The sample above is inside a stored procedure to which @myParameter is passed. It may be null, or a string value. Clearly, when it is a string it needs to be enclosed in quotes, but when it is null it must not be enclosed in quotes. As follows:

select * from [qlik].udf_getStatistic( 'Heights' )

select * from [qlik].udf_getStatistic( NULL )

The question is equally applicable to calling a stored procedure accepting a nullable parameter from dynamic SQL. The examples are from SQL Server.

Answer 1

Just escape the NULL value with an explicit literal NULL, making sure that the quotes are only included when the value is not NULL.

DECLARE @myParameter VARCHAR(10) = 'ABC'

DECLARE @dynamicQuery VARCHAR(MAX)

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'


SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic('ABC')

SET @myParameter = NULL

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'

SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic(NULL)

You might want to escape additional single quotes that might be on your variable, replacing them with double single quotes, so it doesn't break your dynamic build.