March 15, 2002 at 6:07 am
I'm trying (very) to use T-SQL's **** error-handling ins SQLSERVER 7 to establish some error-recovery. I have a master procedure calling a series of procedures which each return @@ERROR. I can then test whether the returned value is non 0
To do this I use in the calling procedure
DECLARE @intRetVal int
EXEC @intRetVal = [procedure name]
IF @intRetVal <> 0 GOTO errhandle
My cunning plan was then to run a set of statements in the label dependent on the Error value in @intRetVal.
Oh dear! Can I read the variable?
Oh no no no .....
Here's some sample code. Any ideas as to how to get it to work?
CREATE PROCEDURE prc_check_dropthru
AS
DECLARE @intRetVal smallint
EXEC @intRetVal = prc_check_dropthru_a
IF @intRetVal <> 0 GOTO errhandle
RETURN
errhandle:
IF @intRetVal = 208
BEGIN
PRINT 'Error Invalid Object Name'
END
ELSE
BEGIN
PRINT 'Pray, Where is the Return Value?'
END
CREATE PROCEDURE prc_check_dropthru_a
AS
SELECT * FROM dbo.crud
RETURN @@ERROR
exec prc_check_dropthru
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 15, 2002 at 9:54 am
December 8, 2002 at 8:47 pm
Look at the doco for SET XACT_ABORT and see if that helps you with your controlling stored proc.
December 9, 2002 at 7:24 am
Error handling is a funny thing in SQL Server. Some errors are not recoverable, and affect the context differently.
In your example the error is not recoverable in context and so is raised in the calling context, this should result in @@error being set in your prc_check_dropthru procedure.
So a combination of both can be used.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply