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

From my understanding, I try this on SQL Server 2012,

CREATE PROCEDURE ToNullProc 
     (@i VARCHAR(20)) 
AS 
BEGIN
    PRINT 'you entered ' + @i
END

CREATE FUNCTION ToNullFun 
     (@i VARCHAR(20)) 
RETURNS @table TABLE (i VARCHAR(20))
AS
BEGIN
    INSERT INTO @table 
        SELECT ('You entered ' + @i) a
    RETURN
END

DECLARE @j VARCHAR(20) = 'Hi',
        @QueryFun NVARCHAR(50) = N'',
        @QueryProd NVARCHAR(50) = N''

IF @j IS NOT NULL
BEGIN
    SET @QueryFun = N'select * from ToNullFun ('''+@j+''')'
    SET @QueryProd = N'exec ToNullProc '''+@j+''''
END
ELSE BEGIN
   SET @QueryFun = N'select * from ToNullFun ('+@j+')'
   SET @QueryProd = N'exec ToNullProc '+@j+''
END

PRINT @queryfun
PRINT @queryprod

EXEC sp_executesql @queryfun
EXEC sp_executesql @queryprod

update for dynamic procedure and dynamic function :

create table #temp (Num int identity (1,1), NullVal int)
insert into #temp (NullVal) values (1),(null),(3)

alter proc ToNullProc (
 @Operator varchar (max), @NullVal varchar (max)
) as
begin 

 declare @Query nvarchar (max) = N'select * from #temp where NullVal ' + 
  @Operator + @NullVal 
 -- print @query + ' ToNullProc print '

 exec sp_executesql @query  -- Here we run the select query from Proc

end

create function ToNullFun (
 @Operator varchar (max), @NullVal varchar (max)
) 
returns nvarchar (max)
as
begin    
 declare @Query nvarchar (max)

 set @Query = N'select * from #temp where NullVal ' + @Operator + @NullVal 

 /* 
  I try to into to Table variable by using ITVF,
  'insert into @table exec sp_executesql @query'.
  But this type of insert is not allowed in ITVF.
*/

 return @query

end

declare @NullVal varchar (max) = '1'
, @QueryFun nvarchar (max) = N''
, @QueryProd nvarchar (max) = N''
declare @FunResultTalbe table (
 Query nvarchar (100)
) /* To store the result Funtion */
if @NullVal is not null
begin
 set @QueryFun = N'select dbo.ToNullFun ('' = '','''+@NullVal+''')'
 set @QueryProd = N'exec ToNullProc '' = '','''+@NullVal+''''
end
else begin 
 set @QueryFun = N'select dbo.ToNullFun ('' is null '','''')'
 set @QueryProd = N'exec ToNullProc '' is null '','''''
end

print  @queryfun + ' At start'
print  @queryprod + ' At start'

exec sp_executesql @queryprod  -- It calls Proc 

insert into @FunResultTalbe
exec sp_executesql @queryfun   -- It calls the Function and insert the query into the table.

set @QueryFun = (select top 1 * from @FunResultTalbe)  -- Here we get the query from the table.
print @queryfun

exec sp_executesql @queryfun  -- Here we run the select query. Which is dynamic

Result sets

-- Result of Procedure
Num NullVal
1   1

-- Result of Function
Num NullVal
1   1

Let me know, what did you got.

Answer 2

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.

Answer 3

The answer is actually different between stored procedures and functions.

From Books On Line or whatever they call it this month (Scroll down a ways):

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. However, the DEFAULT keyword is not required when invoking a scalar function by using the EXECUTE statement.

So for a proc, when you want to pass a NULL parameter, you can just not pass it. For a function, though, you have to tell it to use the DEFAULT value explicitly. Either way, you do not pass it an explicit NULL. Luckily for your dynamic SQL, though, the explicit DEFAULT also works with a stored procedure. In both cases, in order to make sure that the parameters you are passing get assigned correctly, you want to use explicit parameter names in your call.

Let's use this function definition:

CREATE FUNCTION (or procedure) [qlik].udf_getStatistic (
  @param1 integer = 0,
  @param2 varchar(100) = 'foo'
) AS ...

Both parameters are optional. Since this is a function, this call will throw an insufficient number of parameters error:

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

If it were a procedure call, it would throw a cannot convert value 'Heights' to data type integer because it will apply the only parameter value passed to the first parameter it encounters, which is expecting an integer. In both cases, you get what you want this way:

select * from [qlik].udf_getStatistic( @param1 = DEFAULT, @param2 = 'Heights' );

Which brings us to your dynamic SQL. Add your parameter name(s) to the static text, then use COALESCE (or CASE if you like) to decide whether to pass an explicit value, or the DEFAULT call.

DECLARE @myParameter1 VARCHAR(100) = 'foo',
        @myParameter2 INTEGER,
        @SQL NVARCHAR(MAX);


SET @SQL = 
   'select 
      * 
    from [qlik].udf_getStatistic( 
      @param1 = ''' + COALESCE(@myParameter1, 'DEFAULT') + ''', 
      @param2 = ' + COALESCE(CAST(@myParameter2 AS VARCHAR(30)),'DEFAULT') + ' );';

SELECT @SQL;

Result:

select * from [qlik].udf_getStatistic( @param1 = 'foo', @param2 = DEFAULT );