Stored proc question (return)

Stored proc question (return)

Post by Haresh Assuma » Sat, 26 Sep 1998 04:00:00



How do I receive a value that is returned by the stored procedure from SQL
6.5 in my C++ application using ADO. I'm not talking about the recordset
(data) but the return value from the stored proc.

Also, how do I trap errors in a stored proc. For example if my stored proc
has a number of sql commands and one of them fails, I'd like to trap it and
return the error and return an error number.

Thanks,
Haresh

 
 
 

Stored proc question (return)

Post by Ganesan Rajarama » Sat, 26 Sep 1998 04:00:00


Hi Haresh,
To get output parameters from outside enclose your exec call like this:




-Ganesh


> How do I receive a value that is returned by the stored procedure from SQL
> 6.5 in my C++ application using ADO. I'm not talking about the recordset
> (data) but the return value from the stored proc.

> Also, how do I trap errors in a stored proc. For example if my stored proc
> has a number of sql commands and one of them fails, I'd like to trap it and
> return the error and return an error number.

> Thanks,
> Haresh


  vcard.vcf
< 1K Download

 
 
 

Stored proc question (return)

Post by Steven Wor » Tue, 29 Sep 1998 04:00:00


In VB, The ADO object has a number of parameters when the command object is
used. These can be configured to be the input, output, and I think, the
return value of the SP.

Alternatively, try



And the return value will be the last result set.


following example.

CREATE PROC sp_name


AS
BEGIN


UPDATE Table



GOTO TheEnd

:ErrorHandler
-- Error handler code in here



:TheEnd

END
GO

--
Steve Wort

 
 
 

1. Returning a value from a Stored Proc to a Stored Proc

The situation:

I have a stored procedure for our employees database that inserts a new
employee. This procedure executes a SQL query to find the new employee's id
number.

The 'insert new employee' procedure is called by another stored procedure.
In order to do finish the processing in this procedure, I need to have the
employee's id number, which is found in the first stored procedure.

For some reason, I can't figure out a pass the value of the employee's id
number between the procedures.

The following is my code for the AddNewEmployee procedure. I try to specify
the new Employee Number as OUTPUT, but I can't figure out how to access that
value from the procedure that calls this procedure.

Thanks in advance for the help.

CREATE PROCEDURE [AddNewEmployee]

...
...
...


AS

BEGIN TRAN


 Begin
  Print 'Please provide the candidate number'
  Return --Breaks out of stored procedure
 End



 BEGIN
  ROLLBACK TRAN
  RETURN
 END

insert into employees (
 employee_number,
...
...
...
 billable
 ) values (

...
...
...

 )

 BEGIN
  ROLLBACK TRAN
  RETURN
 END

COMMIT TRAN

Return

2. [WEBMASTER] 'www/html/docs awbook.html'

3. Stored Proc Question (rows returned)?

4. HELP! - Do story page in SQL Database or CGI App?

5. Returning output params from a store proc that also return a recordset

6. Failed to call Procedure inside Trigger.

7. Help : RETURN in trigger not returning to stored proc properly

8. How to store and retrieve UTF8 format data to/from SQL Server 2000?

9. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

10. How to get results from Stored Proc within a Stored Proc

11. calling stored proc from stored proc via variable

12. Calling a Stored proc within a stored proc

13. Exec store proc within a store proc