executeStoredProcedureForQuery

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 

Stored Procedure Return Value
is there away to get this or map it correctly 

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 

I cant change the SP definition , since its used in other systems . well do thank you