Calling a Stored Procedure and Passing a NULL as a Valid Value (Magic xpa 3.x)

Objective

Creating a Direct SQL task that does the following:

• Calls a stored procedure that passes a value to an expected string argument

• Sometimes holds a string value

• Expects NULL rather than an empty string as an empty value.

Solution

This example shows an implementation of a stored procedure call that takes one numeric argument and one nullable string.

The normal SP call would be:

EXEC sp_Sample :1, ':2'

When the input arguments are mapped as follows:

:1 to the numeric value

:2 to the Alpha field

This will translate in runtime to an SQL statement of (example):

EXEC sp_Sample 7, 'Support'

However, if the user chooses to pass NULL as the value of the string (2nd input argument), the result will be:

EXEC sp_Sample 7, 'NULL'

This is not a real NULL but a string that holds the value "NULL".

The solution for passing a real NULL would be to manipulate the statement;

Use:

EXEC sp_Sample :1, ':2'

when a real value is passed, but change the command to:

EXEC sp_Sample :1, :2

when passing a NULL.

The trick is to create a command as follows:

EXEC sp_Sample :1

And use this single input argument with a dynamically built string holding all required values:

For example (using variable 'A' as the numeric and variable 'B' as the nullable string):

Str (A,'5') & IF (ISNULL (B),',NULL',','&''''& Trim (B) &'''')

This expression will create the string (when 'A' = 7)

For non-null strings:

7, 'StringValue'

But for null strings:

7, NULL

without using quotes on the second parameter, making the NULL reach the database correctly.