by Haresh Assuma » Sat, 26 Sep 1998 04:00:00
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
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
> 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 |
by Steven Wor » Tue, 29 Sep 1998 04:00:00
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