Stored Procedures - output parms or return codes

Stored Procedures - output parms or return codes

Post by Stuart Dickers » Fri, 12 Jan 1996 04:00:00



I need help!  I need to retrieve either an output parm value or return
code from a stored procedure from SQL Server into a PB variable in a
script.  The stored procedure does not return any rows.  I simply need
to know if it executed successfully.

I tried using the DynamicStagingArea with now avail.  Can anyone share
a script in which they've done this?  Or do you have any other
suggestions.

Thanks in advance.

*****************************************************
Stuart*erson
Senior Consultant
Daugherty Systems - Atlanta
http://www.veryComputer.com/
*****************************************************
400 Interstate N. Parkway Suite 500
Atlanta, GA 30339
phone (770)618-5858 fax(770)618-5859

 
 
 

Stored Procedures - output parms or return codes

Post by je.. » Sat, 13 Jan 1996 04:00:00



> Path: news.i-d.com!news.internetMCI.com!newsfeed.internetmci.com!realtime.net!news.mindspring.com!usenet

> Newsgroups: comp.soft-sys.powerbuilder
> Subject: Stored Procedures - output parms or return codes
> Date: Thu, 11 Jan 1996 15:41:35 GMT
> Organization: MindSpring Enterprises, Inc.
> Lines: 24


> NNTP-Posting-Host: stuartd.mindspring.com
> X-Newsreader: Forte Free Agent 1.0.82

> I need help!  I need to retrieve either an output parm value or return
> code from a stored procedure from SQL Server into a PB variable in a
> script.  The stored procedure does not return any rows.  I simply need
> to know if it executed successfully.

> I tried using the DynamicStagingArea with now avail.  Can anyone share
> a script in which they've done this?  Or do you have any other
> suggestions.

> Thanks in advance.

> *****************************************************
> Stuart*erson
> Senior Consultant
> Daugherty Systems - Atlanta
> http://www.veryComputer.com/
> *****************************************************
> 400 Interstate N. Parkway Suite 500
> Atlanta, GA 30339
> phone (770)618-5858 fax(770)618-5859

Look at the SQLCA return codes.  If the procedure fails, you'll get an error
message.  By the way, if the procedure does not have any return variables, you
can use execute immediate to run your procedure.  A good example is on the CD.

Jeff

 
 
 

Stored Procedures - output parms or return codes

Post by zkjp0 » Tue, 16 Jan 1996 04:00:00


Here are the notes our group uses; bottom line, check sqlcode.

If SP is designed to be called from Powerbuilder:
The raiserror is done within the SP.  This will set the
transaction.sqlcode in PB to -1. In this case, your SP must not return
anything or issue a select statement which display to the screen, because
PB cannot handle that output.  

within PB:  
(assuming youve already created a trans object, with autocommit true)
DECLARE chgauth_check_in procedure for ap_chgauth_check_in

EXECUTE chgauth_check_in;
IF sqlca.sqlcode = -1 THEN  error handling....

within SP:

   raiserror 77777
   return
END

If SP is designed to be called from ISQL or Desktop DBA:
This can be handled like a SP called from PB.  In this case, you can also
issue select statements, which will display their results to the screen.

If one SP is designed to call another SP:
The called SP returns the message number and the calling SP issues the
raiserror.  If an error occurs while calling, a generic error of 99999 is
raised.  In this case, the calling SP then issues a return with the
message number so the calling C program will realize an error has
occurred.

Calling SP:



   ...
END ELSE BEGIN
   /* Rollback any outstanding transactions */

      rollback transaction



      END ELSE BEGIN

         return 99999
      END
   END
END

Called SP:

Notes:  
If there was a Begin transaction issued, you will need to issue a
rollback transaction before the raiserror.
If there were temp tables created, you will need to drop them before
returning.

 
 
 

1. Has anyone implemented a LCS algorithm in a stored procedure or extended stored procedure?

Hello, I'm looking for examples and feedback on people's experiences with
using the LCS algorithm approach to determine match percentages given 2 data
elements.

I'm also looking into alternative matching methodologies (something better
than Soundex)... that we could implement.

If you have sample code or even some benchmarks that would be great as well.
I've mentioned the LCS algorithm for use in a name/address matching task we
are going to be facing and now I've got the job of researching it's use in
SQL Server (SQL 2000 _is_ an option if that helps)!  Anyhow.. I wrote a
simple LCS once in VB/RDO (long time ago) and was examining doing it in an
Extended stored procedure but found I needed C++ skill to write a true XSP
(due to the TDS usage); Unfortunately I wasn't a C++ coder then and I'm
still not... but we've got one on staff! lol...


Remove the obvious prefix if you'd like to contact me.

2. Does RIP route beween modem and NICs?

3. HELP: How to get ret-code, output-parm in dynamic SQL?

4. Unix - Frequently Asked Questions (Contents) [Frequent posting]

5. Return value from an SQL stored procedure

6. Problem Dell DLT1autoloader with windows 2000

7. Stored procedure returning a result set

8. Loosing my connection

9. VO2Ado + Return Parameters from a Stored Procedure

10. SQL - return value from stored procedure

11. HELP - Capturing Stored Procedures Return Values

12. Checking return value from a stored procedure

13. How to get return from stored procedure in "execute immediate" ?