Error handling in Stored Procedures

[simonm]

SQL Server:

I am calling stored procedures from VB using ADO 2.6

I have error handling in my stored procedures but it doesn't seem to do anything as any error in the procedure generates an RT error in VB.

I don't want this to happen as I want to stored procedure to deal with any errors that occur.

Does anybody know what I am not doing?

Simon.

[Ianpbaker]

hi simonm

If you test @@ERROR constant where you want to trap error's you can contain it.

BEGIN TRAN
INSERT INTO TABLE1(field1,field2)
VALUES('a',1)

If @@ERROR <> 0 THEN
ROLLBACK TRAN
ELSE
COMMIT TRAN

hope this helps

Ian

[JHausmann]

you could always wrap your stored procedure call with

On Error Resume Next
'call stored procedure here
err.clear
On Error GoTo Error_routine 'or whatever you normally do for VB errors

[simonm]

If you can only use @@ERROR when you enclose your SQL statements in a transaction?

Thanks for your help,

Simon.

[Ianpbaker]

you can use @@ERROR where ever you want in a SQL statment, I just used the transaction as an example

Ian

[simonm]

My procedures immediately stop upon encountering an error and do not proceed to execute the rest of the procedure.

Would you happen to know why this happens? It's almost as if I need an equivilent of VB's 'ON ERROR RESUME NEXT' so I can test the 'Err' object after each statement.

Simon.

[Ianpbaker]

please post you code and I will have a look for you

[simonm]

CREATE PROCEDURE ChangeName(@ID AS int, @NewName as varchar(50)) AS

DECLARE @Err AS int

BEGIN TRAN

UPDATE t_Employees
SET ix_Name = @NewName
WHERE pk_Employees = @ID

SET @Err = @@ERROR

If @Err <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END

RETURN (@Err)
GO

--------------------------------------------------------

I generate an error with the above procedure by passing in @NewName with over 10 chars as there is a constraint on the table that restricts the length to 10 chars.

I then get this message when I call it:

Server: Msg 547, Level 16, State 1, Procedure ChangeName, Line 7
UPDATE statement conflicted with COLUMN CHECK constraint 'CK_t_Employees'. The conflict occurred in database 'Simon', table 't_Employees', column 'ix_Name'.
The statement has been terminated.

---------------------------------------------------------

Why does it do this instead of just returning the error code as it should do?

Simon.

[Ianpbaker]

it all makes sense. If I remember correctly, you have to test the @@ERROR imediatly after the statement for it to work

Ian