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

sql stored procedure null input parameter
how to handle null parameters in stored procedure
execute stored procedure with multiple parameters
how to pass query as parameter to stored procedure in sql server
sql server get parameter values for stored procedure
how to pass dynamic parameters to stored procedure in sql server
only functions and some extended stored procedures can be executed from within a function
execute stored procedure sql server with input parameters

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.

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.

Using sp_executesql stored procedure for executing dynamic SQL , The sp_executesql stored procedure is used to execute dynamic SQL queries in SQL Server. A dynamic SQL query is a query in string format. price INT NOT NULL In the script above, we declare a variable @SQL_QUERY and function conversion stored procedure · Dynamic SQL in SQL Server  MySQL: Select several rows based on several keys on a given column. mysql,sql,database. If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2

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

SQL Server Stored Procedure with Parameters, the City is passed into the stored procedure so it can create dynamic results. To call this stored procedure we would execute it as follows: Procedure or function 'uspGetAddress' expects parameter '@City', which was not supplied. SQL Server Stored Procedure using NULL as Default Parameter. How To Pass Dynamic Parameters To Stored Procedure In Sql Server

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.

Field = Parameter OR Parameter IS NULL Pattern, If this were a function I'd suggest avoiding multi-statement options, If the procedure is first executed when @Parameter is null then the e.g. with the following example the first call to [dbo]. Dynamic SQL within stored procedure and more parameters, you will find the Dynamic SQL option is clearest,  If a default parameter value is defined in the stored procedure, then simply use the DEFAULT keyword for said parameter in the EXEC statement. It is a lot cleaner than a bunch of IF NULL statements and is better for documentation. CREATE PROCEDURE LotsOfParams (@p1 int = 0, @p2 int = 0, @p3 int = 0)

Stored Procedure null parameter within where clause, I've always been a fan of a dynamic sql approach for this type of problem. it would need to do and then only add in the filters if the provided parameter is not null. EXECUTE sp_executesql @BaseQuery, @ParamList, @p1 = @​Parameter1,  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.

Queries with optional parameters, Typically you see these queries in stored procedures but for the sake of EXEC sp_executesql @sql, N '@FirstName nvarchar(50) In general, for this type of query, the best way to handle it is the dynamic method. To make this change, we’ll add a parameter to our stored procedure, as well as the dynamic query. We’ll use the sp_executesql command to call the dynamic query using these parameters. The updated stored procedure with changes is shown below. The stored procedure parameter is green, and the dynamic query parameter red.

How do I pass parameters to function dynamically and execute , And this function in the stored procedure is working fine. Now, I am planning to use the same function in another stored proc, this time I passed  SQL Query to Select All If Parameter is Empty or NULL. In this example, we used the IIF Function along with ISNULL. First, the ISNULL function checks whether the parameter value is NULL or not. If True, it will replace the value with Empty string or Blank. Next, IIF will check whether the parameter is Blank or not.

Comments
  • I dropped the null tag you had on the question and added sql-server to get more eyes on this, just in case anybody else had better thoughts on it.
  • @codecabbie.. find my updated answer