How to pass a null input for Execute Stored Procedure plugin expression?

Hello!

I am using 16.2 and SQL server. I have a stored procedure with 4 inputs and those all 4 inputs can be null. The SPROC is defined to return summation of few columns. When I am executing the stored procedure in SQL server with all inputs as null, it's returning proper data.

However, when I tried to run the SPROC with null inputs via executeStoredProcedure plugin expression, it returns just null values.
How can I get the actual results by passing null values to a stored procedure?

Thank you!

OriginalPostID-260253

  Discussion posts and replies are publicly visible

Parents
  • Thanks for your inputs. I solved the issue by modifying the where clause in the stored procedure. In the where clause, I added following conditions for each field:
    WHERE (field_name1 LIKE @input1 or field_name1 IS NULL or field_name1 = '')

    Initially, I had only (field_name1 LIKE @input1 or field_name1 IS NULL).
  • 0
    Certified Lead Developer
    in reply to Omkar K

    Hi Omkar,

    I think you were trying this from a Smart Service, where we can pass the parameter of proper type by keeping them null and it works fine.

    From Expression function:

    When I initially tried passing an initialized parameter of type integer having a null value it, it always returned the below exception

    [success:false,error:The conversion from UNKNOWN to UNKNOWN is unsupported.]

    I had a look into the expression function and there is no validation for if a input parameter is passed or not.
    Hence as a workaround if you are evaluating a stored procedure from an expression function, and need to pass a null, just don't map that as an input.

    Although in the doc it says they should be defined for functions as well.

Reply
  • 0
    Certified Lead Developer
    in reply to Omkar K

    Hi Omkar,

    I think you were trying this from a Smart Service, where we can pass the parameter of proper type by keeping them null and it works fine.

    From Expression function:

    When I initially tried passing an initialized parameter of type integer having a null value it, it always returned the below exception

    [success:false,error:The conversion from UNKNOWN to UNKNOWN is unsupported.]

    I had a look into the expression function and there is no validation for if a input parameter is passed or not.
    Hence as a workaround if you are evaluating a stored procedure from an expression function, and need to pass a null, just don't map that as an input.

    Although in the doc it says they should be defined for functions as well.

Children
No Data