Call a Stored Procedure or Function from Dynamic SQL - with Nullable 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
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
CASE if you like) to decide whether to pass an explicit value, or the
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;
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 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.
- I dropped the
nulltag you had on the question and added
sql-serverto get more eyes on this, just in case anybody else had better thoughts on it.
- @codecabbie.. find my updated answer