Error Trapping - How To in procedures

[EdMacDonald]

I want to trap certain errors (e.g. unique constraint violations for insert) in my SQL procedures and handle them gracefully by returning a user defined error code to the caller.<br><br>Using @@Error lets me view the error code, but still upsets the client by raising a nasty exception.<br><br>In Oracle I would use the BEGIN, EXCEPTION, END block format.&nbsp;&nbsp;Is there an equivalent that I can't seem to find?<br><br>Thanks,<br><br>Ed

 

[shootsie]

Have you found a way to do this? Thanks!

-Shootsie

 

[bperry]

Hi there,
I've been doing some work in ASP, so I can tell you what errors feel like from that client. It sounds like you're working in Dephi, VB, something like that, so this may not apply much....

Anyway, when running sql or stored procedures from ASP via ADO, constraint violations etc definitely generate errors, and these come back as error parameters in the ADO connection object. But at that point, it's up to the designer to decide what the client program should do with that error. You could do nothing, not notify the client, and he would be unaware that an error had occurred at all.
What I do is check the errors on the connection, and when I see some then I put out a custom 'error' html page, telling him something tragic occurred, and display some of the info provided by the server message (error #, error test, etc).

But like I said, in ASP I can just ignore the error if I wanted to (which is not normally a good idea)

Do you have that kind of control in your client, where you can check for errors and program some pre-determined action to take?

If this is totally useless comment to you, please ignore!

bperry

 

[eguthrie]

I have a similar problem. In a stored procedure, I'm inserting multiple records into a SQL Server table which has a unique constraint on 2 columns. If the record insert violates the constraint, I want it to just skip that record and go on to the next record.

I can also see the value of @@ERROR, but am not able to trap the error to deal with it. Are some errors not trappable?

 

[tlbroadbent]

Some SQL Server errors are FATAL and stop further execution of the SQL script. These errors cannot be trapped in SQL code because SQL Server stops processing the code.

The best way that I've found to avoid FATAL errors during insert statments is to include criteria that check if a record with the unique key already exists. Here are some examples.

Insert table2
Select colA, colB, colC, ... colQ
From table1
Where Not Exists
(Select * From table2 t
Where t.colA=table2.colA And t.colB=table2.colB)

Insert table2
Select t1.colA, t1.colB, t1.colC, ... t1.colQ
From table1 t1
Left Join table2 t2
On t1.colA=t2.colA And t1.colB=t2.colB
Where t2.colA Is Null

Insert Table2
Values (21, 'abc', 109.55, 'brown')
Where Not Exists
(Select * From table2
Where ID=21 And type='abc')

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[jirle]

Trying to write error trapping into existing stored procedures that will write the name of the sp causing the error, any error message, date time stamp to a table. Would ideally also like to report that there has been an error back to the client (ASP/VB Web). I am new to SQL and am totally in over my head. Below is what I have managed to figure out. However, I don't know. I am constrained to keeping this error trapping in the SP, I cannot change architecture. PLEASE HELP!

<<START PROCEDURE>>
DECLARE @ErrorSave INT
SET @ErrorSave = 0
<<sp code>>
IF (@@ERROR <>0)
begin
SET @ErrorSave = @@ERROR

DECLARE @MyStoredProc NVARCHAR (50)
SET @MyStoredProc = 'sp_NAME'

RETURN @ErrorSave & @MyStoredProc

<<This is where I would like to put this stuff in the error table>>
PRINT current_Timestamp
PRINT 'Object Name: '
PRINT @MyStoredProc
PRINT 'has produced an error. Copy this entire message into an email and send to your HR Representitive or the Sources Help Desk.'
end

 

[krotha]

Hi tlbroadbent,
Here I have a similar situation and I tried to use your 3rd example and I get error:
Incorrect syntax near the keyword 'Where'.
Can you please tell me what I am doing wrong?

insert into attachments_xref(doc_id,project_id,site_id)
values (1021,2065,74)
where not exists (select * from attachments_xref where doc_id=1021 and site_id=74
and project_id=2065)

 

[tlbroadbent]

When you use the values clause you cannot use a where clause. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.