reading return codes from stored procedure execution

We currently have a pb application that executes a stored procedure as follows:

The stored procedure is declared in the transaction object as a local external function:
   function long abs_s_admin_recalc(datetime statement_date,string credit_account_nmbr, string retro, string calc_type, decimal data_origination_cd_in, decimal data_origination_cd_out) RPCFUNC ALIAS FOR "dbo.abs_s_admin_recalc"

It is then executed in the app as follows:
SQLCA.AutoCommit = TRUE

ll_rc = SQLCA.abs_s_admin_recalc(DateTime(idt_end_dt),"ALL","N",'both',3,19)

If SQLCA.SQLCode < 0 then
    generate error
    SQLCA.AutoCommit = FALSE
    return -1
end if

SQLCA.AutoCommit = FALSE


I've added return code processing to the stored procedure by putting statements as follows after each sql:

        IF (@@error <> 0)
      RETURN -56

I want to pull this return code when I get back to my app.  I'm not understanding how to obtain the error code.  I don't see it in sqlca and I don't get it in my ll_rc value either on my execute statement.

How do I get the actual value I returned?


BTW, I'm using a sql2000 database.
babybirdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
I am not sure if sqlca.sqldbcode is the one. This is a vendor specific err code. I thought ll_rc would return the value returned using RETURN statement in Stored Proc.
babybirdAuthor Commented:
If the proc takes a database error,  I'm able to identify that by checking sqlca.sqlcode and the database error is in sqldbcode.  But I'm not able to obtain the error I specified on the return statement.  This would be more helpful so I know specifically where in the proc it failed.  ll_rc comes back = 0 regardless of whether it errors or not.  

namasi_navaretnamCommented:
When you run the strored proc from query analyzer to get the right return code?

i.e
declare @rc
exec @rc = sp_yourproc @parm1, @etc
select @rc

Does you stored proc has a single exit point or mutiple exit point? Do you have a return statement at the end of proc?

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

namasi_navaretnamCommented:
Sorry. Should be.
@rc  = exec  sp_yourproc @parm1, @etc
namasi_navaretnamCommented:
How is this sp declared in PB. RPCFUNC?
Vikas_DixitCommented:
Hi,
 Can you try getting the return value as an out paramater ??, Or by using the Declare procedue method instead ??

Regards,
Vikas Dixit
namasi_navaretnamCommented:
I have sp declared as a RPCFUNC on n_tr (PFC App) as a local external function and I get the return code from sp without any problem.

Example:

function long f_myfunc(long my_id,string my_string) RPCFUNC ALIAS FOR "dbo.sp_myproc"

Or as Vikas suggests,

DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken

@var_date_1 = :ad_start,
      @var_date_2 = :ad_end,
      @rtn = :ll_rc OUTPUT;

In stored proc, before exiting you will need to,
select @rtn = -1 // or whatever

HTH

Namasi

gajender_99Commented:
hi

when you are declaring your function you have to declare it as a subroutine where i have added the last argument as REF DOUBLE return_value"

"SUBROUTINE ABS_S_ADMIN_RECALC(DATETIME STATEMENT_DATE,STRING CREDIT_ACCOUNT_NMBR, STRING RETRO, STRING CALC_TYPE, DECIMAL DATA_ORIGINATION_CD_IN, DECIMAL DATA_ORIGINATION_CD_OUT, REF DOUBLE RETURN_VALUE ) RPCFUNC ALIAS FOR "DBO.ABS_S_ADMIN_RECALC" "
AS A LOCAL EXTERNAL FUNCTION

double ll_rc
the variable ll_rc should be the last argument int you subroutine which can hold back the return value.

SQLCA.abs_s_admin_recalc(DateTime(idt_end_dt),"ALL","N",'both',3,19,ll_rc)

If SQLCA.SQLCode < 0 then
    generate error
    SQLCA.AutoCommit = FALSE
    return -1
end if

if ll_rc = -56 then
  // your condition
Else
//your condition
End if

one more thing to rembember
the PROCEDURE which you are exceuting should have the last argument defined as
IN OUT INTEGER
//Example
ABS_S_ADMIN_RECALC(a in integer, b in integer, v_status IN OUT INTEGER)

so you can always return a value in V_STATUS  and get that value in ll_RC

I am working on this type of procedures for around three years.
if you have any problem let me know. i can give you a detail example.

thanks
Gajender


sandhiyaaCommented:
Hi,
U cannot get the value of the return code that u have used in the procedure.
sinc sqlcode returns u the prefined value ie 1 ,0,100.
i got the same problem.
i solve it ,by using ouptput parameter and according to the parameter if displayed the error message and placed the cursor.

Regards,
sandhiya
gajender_99Commented:
Hi Sandhiyaa

the SQLcode we use is to check weather our  procedure worked or not.
I we wnat to return a value from the procedure then we have to use a in and out variable in the calling of the procedure.
this is what i have explained.

thanks Gajender

berXpertCommented:
Hi,

if you want your code catch your own error code, try to use "raiseerror" statment in your stored procedure, here's an example:



declare  @errno   int,
         @errmsg  varchar(255)

/* Make a validation and RAISE the error*/
IF  ( 1 < 2 )
begin
        select @errno  = 30056,
        @errmsg = 'Put your message here, it will appear in SQLCA.SQLErrText '
        raiserror @errno @errmsg
        rollback transaction
        return
end



Hope this helps,
berX

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.