hello i am using this executeStoredProcedureForQuery but I'm not receiving anything in results this SP is SQL i want to get this i tried using the smart service and its the same no values returned
Discussion posts and replies are publicly visible
Are you executing the stored procedure using the same account? Could it be permissions?
Otherwise, what DB type are you using? There are certain DBMSs that have specific behaviours (i.e. case sensitivity) that you need to look out for.
yes i created a connected system that uses service account (its the same one ), it has permission to execute DB is SQL Server Data Source the SP name is the same as the one in DB
Can you share the definition of the stored procedure?
i only have the permission to the execute i cant access the create
USE [Main]GO
DECLARE @return_value int
EXEC @return_value = [dbo].[Check_SpecialCasesContractStatus] @LaborerIdNo = N'1048518717'
SELECT 'Return Value' = @return_value
GO
Then any king of debugging becomes difficult from the Appian side of things.
would it affect if the SP definition doesn't have a result set (no select statements at the end )only return
Looking back it looks like maybe your stored proc may not be working properly?
"In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error."
exactly these values are returned -1,-2 when i check it from the MSSMS but when i use the Smart service its null ,how am i supposed to get this value
As stated by the blurb I posted, non-zero values indicate an error.
I can't say why it returns success with an empty result in the expression but before investigating that, I would investigate why your stored proc returns an error when running in MSSMS.
the stored procedure checks for the statue of a request if its pending it return -2 which is an error to stop him from creating another request but my issue is i am not getting the
Personally, I would return a result instead of returning an error. So return code 0 but a result of "Already Exists".
Otherwise, perhaps you could log a case with Appian support to indicate that they seem to be returning a success when the procedure returns a error in SQL Server stored procedures.
I cant change the SP definition , since its used in other systems . well do thank you