Error Trapping within Store Procs 2

[Robinstwitcher]

I have a multiple store procs that are called from a 'controlling' store proc. This is working fine, however I am trying to introduce Error trapping. This is all fairly new to me and I need some advice as to what I should be looking for.
So far I have been using:

CREATE PROCEDURE Control AS
DECLARE @retStatus int

exec @retstatus = [Upload]
if @retstatus !=0 begin
GOTO ErrorMsg
exec @restatus = [Update]

ErrorMsg:
PRINT 'Return Status is '+cast(@retsatus as nchar(10))
GO

and

CREATE PROCEDURE Upload AS

INSERT INTO Tbl2
(col1,col2)
SELECT Col1, Col2
FROM Tbl1

RETURN @@Error
GO

This works Ok for a truncation error (returns status 8152) , however a Disallowed implicit conversation............... returns a status of 0 (i.e. no error)

Am I doing something drastically wrong!

 

[bborissov]

CREATE PROCEDURE Control AS
DECLARE @retStatus int

exec @retstatus = [Upload]
if @retstatus !=0 begin
   GOTO ErrorMsg

exec @restatus = [Update]
if @retstatus !=0 begin
   GOTO ErrorMsg

GOTO Success -- No error we must exit SP w/o error message

ErrorMsg:
PRINT 'Return Status is '+cast(@retsatus as nchar(10))

Success:

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Sorry I forgot write other code:

CREATE PROCEDURE Upload AS
DECLARE @lnError int
SET @lnError = 0

INSERT INTO Tbl2
         (col1,col2)
SELECT Col1, Col2
FROM Tbl1
SET @lnError = @@Error
IF @lnError <> 0
   RETURN @lnError
ELSE
   RETURN 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Robinstwitcher]

bborissov,

thanks for that, have copied your code - I get an "Incorrect syntax near 'success:'"

I am not sure if there is anymore code after it? or whether I need to add some "end" points?

 

[Robinstwitcher]

I have added End after the GOTO ErrorMsg lines.
However, if i change Tbl1, Col2 from a numeric to SmallDateTime and leave Tbl2, Col2 as an int.
I get:
Server: Msg 260, Level 16, State 1, Procedure Upload, Line 7
Disallowed implicit conversion from data type smalldatetime to data tye bit, ..........................

Although this is a very unlikely situation, why is the @retStatus 0? shouldn't the @@error return 260?

 

[ptheriault]

Robin,
Is this for SQL 2005? If so you can use...

BEGIN TRY
 Your CODE Here
END TRY
BEGIN CATCH
  IF @@ERROR <>0
  BEGIN
    DECLARE @ErrMsg nvarchar(4000),
            @ErrSeverity int
    SELECT @Errmsg = ERROR_MESSAGE(),
           @ErrSeverity = ERROR_SEVERITY()
    RAISERROR(@ErrMsg,@ErrSeverity,1)
  END
END CATCH

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Robinstwitcher]

Paul,

Afraid not, still in the 2000 era!

 

[ptheriault]

bummer, That's just one of the many areas that is much improved in SQL 2005.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

read this

http://www.sommarskog.se/error-handling-II.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Robinstwitcher]

Thanks for help, after reading the link changed code to:

CREATE PROCEDURE Control AS
DECLARE @retStatus int

SET NOCOUNT ON

EXEC @retstatus = [Upload]
SELECT @retStatus = coalesce(NULLIF(@retStatus, 0), @@error)
IF @retstatus <> 0 begin
GOTO ErrorMsg RETURN @retStatus END

EXEC @retstatus = [UpDate]
SELECT @retStatus = coalesce(NULLIF(@retStatus, 0), @@error)
IF @retstatus <> 0 begin
GOTO ErrorMsg RETURN @retStatus END

GOTO Success -- No error we must exit SP w/o error message

ErrorMsg:
PRINT 'Return Status is '+cast(@retsatus as nchar(10))

Success:



So far this has passed all testing! Thanks again