Error Trapping

[TomR100]

Hello,
I have a stored procedure where I insert data into a table. At the end of this insert I want to make sure that there are no errors. Can you tell me how to do this?

Here is my code:

DECLARE @Return int
SET @Return = 0

INSERT INTO tblQrtCHC (HeaderRecord)
/*---------------------------------------*/
/* Create Contribution Header */
/*---------------------------------------*/
SELECT CONVERT(CHAR(100),
'00' + --2,2
'USFC '+ --12,14
SPACE(86) --86,100
) AS HeaderRecord
/*--------------------------------------*/
/* Append Employee Contribution Detail */
/*--------------------------------------*/

UNION ALL

SELECT DataRecord
FROM tblCHCEmployee

I do not know what to do with my local variable @Return to get an alert if something on the insert goes wrong.

Thank you in advance.

TomR100

 

[SQLSister]

Look up @@Error in Books Online, there is a topic on how to use it.

 

[SemperFiDownUnda]

Not that @@Error gets reset every time you issue a SQL command so if you do something like

INSERT INTO BLAH .....

IF @@ERROR <> 0 
  BEGIN
    PRINT @@ERROR
  END

this will only ever PRINT 0 since "IF @@ERROR <> 0" will always reset the @@ERROR global variable.

So do this

INSERT INTO BLAH .....
SET @iMyError = @@Error
IF @iMyError OR <> 0 
  BEGIN
    PRINT @iMy Error
  END

Note that Errors in SQL server stack. Thus if you write a procedure and it encounters 20 errors the client can get all 20.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first