do you need @@error check? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

do you need @@error check?

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2002-12-05 : 17:16:42

Hi,
I have written a number of sprocs which insert/update/delete records. After each statement I have been checking the @@error value to see if something went wrong. If the insert failed though, will the transaction roll back even if I don't use the "GOTO errorblock" statement? If I wanted to raise my own errors I could see using the GOTO errorblock statement but if it is a normal SQL error do I need it?

CREATE PROCEDURE uc_insert_tblRole
-- dynamically generated sproc used for inserting records into tblRole
(
@role_key int
,@role_name varchar(50)
,@role_enabled bit
,@role_editable bit
)
AS
DECLARE
@errno int,
@errmsg varchar(200)
SET @errno = '50005'
SET @errmsg = 'Error inserting record'

BEGIN TRAN

INSERT INTO tblRole
(
role_name
,role_enabled
,role_editable
)
VALUES
(
@role_name
,@role_enabled
,@role_editable
)
--check for error
IF(@@ERROR<>0) GOTO errorblock

--Success. Commit transaction
COMMIT TRAN
RETURN(0)

errorblock:
--An error occurred. Rollback transaction
ROLLBACK TRAN
RAISERROR @errno @errmsg

GO

Here is another stored procdure (from a different database where I use return values instead of raising the error), once again is the @@error check necessary?

CREATE PROCEDURE mc_modUserCustProg
-- This sp is used to update customer information after modifying program information
-- It updates the boeckh and acord fields
(
@cust_key int,
@cust_boeckh bit
)
AS
BEGIN TRAN

UPDATE tblUserCust
SET cust_boeckh=@cust_boeckh
WHERE cust_key=@cust_key
-- check for error
IF (@@ERROR <> 0) GOTO Lerr

-- Success. Commit the transaction.
COMMIT TRAN
RETURN(0)

Lerr:
-- An error occurred. Rollback the transaction.
ROLLBACK TRAN
RETURN(674)
GO

What text can I remove from the sprocs and keep the same functionality? In most sprocs I update multiple tables so I want to make sure everything rolls back if there is an error. (I don't think I need all the error checking but I want to confirm, before removing it all).

Thanks

Nic

Edited by - nic on 12/05/2002 17:18:14

Edited by - nic on 12/05/2002 17:19:04

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-05 : 17:46:57

It will only rollback if you specify it to rollback, but you don't need the GOTO in order for it to happen. You could just do this:

IF @@error <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

You do need to check @@error though to determine if a rollback should occur. Otherwise, how would you know to commit or not?

Edited by - tduggan on 12/05/2002 17:48:17

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-05 : 20:51:20

For the SPs you have there it is not necessary, nor are the explicit transactions.
An sql statement either succeeds or fails (is atomic) - if there is an error then the statement will have failed so there is nothing to commit or rollback.
The error will also be received by the client so there is no need for the raiserror unless you have a specific error to raise.
If you have more than a single update then you will need an explicit transaction and to check @@error after each statement.
This will do just as well but will return the sql server error.

CREATE PROCEDURE uc_insert_tblRole
-- dynamically generated sproc used for inserting records into tblRole
(
@role_key int
,@role_name varchar(50)
,@role_enabled bit
,@role_editable bit
)
AS

INSERT INTO tblRole
(
role_name
,role_enabled
,role_editable
)
VALUES
(
@role_name
,@role_enabled
,@role_editable
)
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nic
Posting Yak Master

209 Posts

Posted - 2002-12-06 : 17:08:35

thank you for your response.

Is there a way to return the error I throw as the primary error? What I mean is, if you look at the 1st sproc I provided (original posting), I check for errors after the insert. If so, I go to errorblock and throw the error 'Error inserting record'. If I run this sproc(after making sure it will fail), two errors occur. The first one is the real sql server error AND the second error is the one I throw.

I would like to trap the second error and display its message instead of the sql generated error message (essentially the real sql server error contains more info than I want the user to see).

I like being able to have the error message in the sproc since most sprocs are more complicated than the ones I provided and I could have different error messages for different statements.

In the end, I just want to provide to the user a more "friendly" message.

Thanks

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources