Is it possible to get return code from SQL stored procedure?

Question

I have Microsoft SQL server stored procedure. Is it possible to get return code from stored procedure?

Please see code below:
I need to be able to analyze return code
Thank you in advance

Michael

create proc test
as
            declare @err int
            insert into table( a,b,c ) values ( ‘a’,’b’,’c’ )
            set @err = @@error
            if @err <> 0 return @err
            .
            .
            .
            .
            return 0

Answer 1

Yes, this questions has an example of how to capture the return value as well as output parameters.  If you only need the return value, drop the output parameters.  https://www.qvera.com/kb/index.php/704/how-to-call-a-stored-procedure-with-output-parameters?show=704#q704

Also note that as of version .48, support for OUTPUT parameters has been added to QIE.  Version .51 also adds support for calling stored procedures that do not contain SET NOCOUNT ON.  Both of these enhancements make it easier to capture the return value.  See this question:

Using a Stored Procedure with a Return Status in QIE?

Additional examples can be found in this question:

How to call a stored procedures in QIE?

For reference the previous answers in this question use TSQL and a non parameterized code wizard method to call the stored procedure.  The examples in the questions above use new pQuery methods in the Code Wizard.  The examples in this question are still supported but the pQuery methods in the Code Wizard are easier to use.  Examples can be found in the linked questions above. 

Answer 2

First, decide if a stored procedure is needed. In QIE use qie.doUpdateQuery and wrap it in a try catch like this:

try {
  var result = qie.doUpdateQuery('AdventureWorks2012', "INSERT INTO\n" +
    " [Production].[ProductReview] ([ProductID],[ReviewerName],[ReviewDate],[EmailAddress],[Rating])\n" +
    "VALUES (1,'rich',CURRENT_TIMESTAMP,'rich@test.com',30)");
  //The next statement is never called because the rating is constrained to a value from 1 - 5
  qie.debug("*** result: " + result);
} catch (err) {
  qie.debug("*** error ***" + err);
}

Note: The above insert fails because the rating is outside the range of 1 - 5. Passing 30 causes it to fail with this error:

com.qvera.qie.exception.NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the CHECK constraint "CK_ProductReview_Rating". The conflict occurred in database "AdventureWorks2012", table "Production.ProductReview", column 'Rating'.
Query: INSERT INTO
[Production].[ProductReview] ([ProductID],[ReviewerName],[ReviewDate],[EmailAddress],[Rating])
VALUES (1,'rich',CURRENT_TIMESTAMP,'rich@test.com',30)

If a stored procedure is required, there are couple things that need to change.

First, stored procedures with INSERT/UPDATE/DELETE statements present a problem because they return a rows affected count. Use the "SET NOCOUNT ON" either within the stored procedure itself or with in the script passed to qie.doQuery as a workaround.

In QIE:

var sp_call =
"SET NOCOUNT ON\n" +
"BEGIN\n" +
"DECLARE @return_value int,\n" +
" @rCode char(1)\n" +
"\n" +
" EXEC @return_value = [dbo].[richRatings]\n" +
" @code = " + rating + ",\n" +
" @rCode = @rCode OUTPUT\n" +
"\n" +
" SELECT\n" +
" @return_value as N'return_value',\n" +
" @rCode as N'rCode'\n" +

"END";

or in stored procedure:

CREATE PROC [dbo].[richRatings]
( @code INT,
@rCode CHAR OUTPUT
)
AS
SET NOCOUNT ON

SET @rCode = 'A';
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
BEGIN TRY
INSERT INTO [Production].[ProductReview]
([ProductID],[ReviewerName],[ReviewDate],[EmailAddress],[Rating])
VALUES
(1,'rich',CURRENT_TIMESTAMP,'rich@test.com',@code)

END TRY
BEGIN CATCH
SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
IF @ErrorVar <> 0
BEGIN
SET @rCode = 'E'
END

IF @RowCountVar = 0
BEGIN
SET @rCode = 'E'
END


END CATCH;
RETURN 0

GO

Second, the variables declared in this example stored procedure are just for illustration. When called via a JDBC driver, @rCode, @ErrorVar, and @RowCount may not have been set without the try catch blocks.