• State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 5 subscribers
  • Views 9343 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Plug-Ins

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

Omkar K
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

  • 0
    Omkar, I would recommend to save the input passed to store procedure in a dummy table to know how it is interpreted when it is sent via executeStoredProcedure expression.
  • 0
    Certified Lead Developer
    Hi Omkar,

    I tried replicating your issue and i didn't have SQL server i used MYSQL and it worked fine for me and returned results.
    I am attaching screenshots with SPROC code in a zip for your reference and it would be helpful if can attach screenshots or code snippet of your code.

    The reason for your procedure for not returning values might be the values you are passing from appian might not be nulls they might contain spaces.

    As suggested by vinayak in the above comment try to add the results to a dummy table so that you can check the behaviour after the execution of the procedure.

    Hope this helps you.

    Forum Post.7z

  • 0
    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.

© 2024 Appian. All rights reserved.