Error handling in Stored Procedures

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.

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

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

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

Thanks for your help,

Simon.

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

Ian

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.

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

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.

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

Ian