SQL Server Stored Proc call with Null parameters

[wdchristensen]

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 @Stef_Hermans = 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)',
@Stef_Hermans= '$(vNull)',
@Anonymous= NULL,
@GHI = NULL(),
@BOOL1 = 0,
@BOOL2 = 1

[marcus_sommer · Accepted solution]

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

[wdchristensen]

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;

[wdchristensen]

I stumbled across this gem which explains the bizarre QS behavior.

https://community.qlik.com/t5/Qlik-Healthcare-User-Group/When-is-a-Result-Set-not-a-Result-Set/gpm-p...

When I contacted support regarding the “why”, they were as perplexed as I was. It is reassuring to know “the truth is out there” and subpar solutions can succeed.