Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Highlighted
wdchristensen
Valued Contributor

SQL Server Stored Proc call with Null parameters

I am trying to call a stored procedure in SQL Server that requires multiple parameters. Some of the parameters need to be NULL. If I attempt to pass the value using the keyword Null it converts the value to a string ‘NULL’ which is not what I need. So I attempted to set a variable using the NULL() function  and the syntax appears fine but in the log file it appears to convert the value to '' which is not the same as null. Any ideas on what I can do to work around this issue, aside from altering the store proc to convert a value of '' to null (SET @abc = IIF(LEN(@ABC )=0,NULL,@ABC ) /* This is to fix a limitation with Qlik Sense loading nulls */) 

SET vDateStart = '11/1/2019';
SET vDateEnd = '11/15/2019';
Let vNull = Null();

Example_Extract:
LOAD *;
SQL EXEC SomeDB.[dbo].[MyStoredProc]
@STARTDATE = '$(vDateStart)',
@ENDDATE = '$(vDateEnd)',
@abc= '$(vNull)',
@def= NULL,
@GHI = NULL(),
@BOOL1 = 0,
@BOOL2 = 1

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: SQL Server Stored Proc call with Null parameters

I'm not sure if it could be directly solved without additionally measures. Also if it really should be considered as a failure because null() is in Qlik a special construct which couldn't be stored or assigned - which was an intentional design decision.

This means:

Let vNull = Null();

will delete the variable vNull.

The assigning of NULL assigned just a string. The use of a NULL-function may work - because Qlik doesn't execute any SQL else it transfers just the statement to the db - if your database evaluates the function before it executes the query - but you need to use a valid null-function from your db, null() is a Qlik function.

Another trial would be to transfer nothing, like:

...
@abc=,
@def=,
...

- Marcus

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

Re: SQL Server Stored Proc call with Null parameters

I'm not sure if it could be directly solved without additionally measures. Also if it really should be considered as a failure because null() is in Qlik a special construct which couldn't be stored or assigned - which was an intentional design decision.

This means:

Let vNull = Null();

will delete the variable vNull.

The assigning of NULL assigned just a string. The use of a NULL-function may work - because Qlik doesn't execute any SQL else it transfers just the statement to the db - if your database evaluates the function before it executes the query - but you need to use a valid null-function from your db, null() is a Qlik function.

Another trial would be to transfer nothing, like:

...
@abc=,
@def=,
...

- Marcus

View solution in original post

wdchristensen
Valued Contributor

Re: SQL Server Stored Proc call with Null parameters

Turns out Qlik handles the NULL just fine 😊, so thanks Marcus for your help and clarification of the Null function in my script. My problem was another fun QS peculiarity, NOCOUNT was not explicitly set in the stored procedure and for some crazy reason QS returns nothing and conveniently produces no warnings or errors. Since I can't image a scenario where this "Functionality" could be useful, I reported it to Technical Product Support. I fully anticipate hearing back it is "working as designed" in a couple days. Thanks for reading! 

SET NOCOUNT ON;