Trapping and processing errors within stored procedures (SQL 7.0)

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Ioannis Demetriade » Tue, 13 Nov 2001 22:31:10



Hi,

I am trying to write a stored procedure that inserts a record into a table.
If that record is already stored in that table, i just want to update it.
However, I don't want to test whether that record exists before I do the
insert (the reason being that 99% of the records will not exist in the db at
that time). In the case where the record exists, I want to trap the error
message  that is raised and handle it internally within the stored procedure
and I don't want to return an error to the calling process.

If I were to do this in an informix database i would have done something
like:

Begin
    On Exception In (<error message for 'record exists'>)
        Update Orders
        Set Status = 4
        Where orderid = 1;

    End Exception

    Insert Into Orders (1,2,3,4)
End

Thanks for your help.

Ioannis Demetriades

 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Raymond D'Anjo » Tue, 13 Nov 2001 22:49:55


Hi,
Why not test for the record inside the SP instead of trapping the error:

IF  (SELECT ...) is null
    insert record
ELSE
    update record

'or you can use IF EXISTS
IF EXISTS (SELECT ...)
    update record
ELSE
    insert record


> Hi,

> I am trying to write a stored procedure that inserts a record into a
table.
> If that record is already stored in that table, i just want to update it.
> However, I don't want to test whether that record exists before I do the
> insert (the reason being that 99% of the records will not exist in the db
at
> that time). In the case where the record exists, I want to trap the error
> message  that is raised and handle it internally within the stored
procedure
> and I don't want to return an error to the calling process.

> If I were to do this in an informix database i would have done something
> like:

> Begin
>     On Exception In (<error message for 'record exists'>)
>         Update Orders
>         Set Status = 4
>         Where orderid = 1;

>     End Exception

>     Insert Into Orders (1,2,3,4)
> End

> Thanks for your help.

> Ioannis Demetriades



 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Ioannis Demetriade » Tue, 13 Nov 2001 23:00:11


I could probably do that -thanks, but if i wanted to trap an error message
within a SP how would i do it?


> Hi,
> Why not test for the record inside the SP instead of trapping the error:

> IF  (SELECT ...) is null
>     insert record
> ELSE
>     update record

> 'or you can use IF EXISTS
> IF EXISTS (SELECT ...)
>     update record
> ELSE
>     insert record



> > Hi,

> > I am trying to write a stored procedure that inserts a record into a
> table.
> > If that record is already stored in that table, i just want to update
it.
> > However, I don't want to test whether that record exists before I do the
> > insert (the reason being that 99% of the records will not exist in the
db
> at
> > that time). In the case where the record exists, I want to trap the
error
> > message  that is raised and handle it internally within the stored
> procedure
> > and I don't want to return an error to the calling process.

> > If I were to do this in an informix database i would have done something
> > like:

> > Begin
> >     On Exception In (<error message for 'record exists'>)
> >         Update Orders
> >         Set Status = 4
> >         Where orderid = 1;

> >     End Exception

> >     Insert Into Orders (1,2,3,4)
> > End

> > Thanks for your help.

> > Ioannis Demetriades


 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Dan Guzma » Tue, 13 Nov 2001 23:27:04


Quote:> I could probably do that -thanks, but if i wanted to trap an error message
> within a SP how would i do it?

AFAIK, you cannot suppress the error; it will still get raised to the
client.  It is the responsibility of the application to ignore (or handle)
the error.  If you can't change the application, I'd go with Raymond's
suggestion and use IF EXISTS.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Dejan Sark » Tue, 13 Nov 2001 23:43:03


Just a small addition to Dan's post. You can't suppress the error message if
you violate the PK, as Dan stated. T-SQL does not have expressions like Try
or OnException. But you can simulate them with the GOTO command:

CREATE TABLE dbo.t1
(
 id int NOT NULL PRIMARY KEY,
 name char(10)
)
GO



BEGIN TRAN


   GOTO ON_ERROR
ELSE
   GOTO NO_ERROR
ON_ERROR:

NO_ERROR:
COMMIT

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.


Quote:> > I could probably do that -thanks, but if i wanted to trap an error
message
> > within a SP how would i do it?

> AFAIK, you cannot suppress the error; it will still get raised to the
> client.  It is the responsibility of the application to ignore (or handle)
> the error.  If you can't change the application, I'd go with Raymond's
> suggestion and use IF EXISTS.

> Hope this helps.

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------

 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Karl Johnso » Wed, 14 Nov 2001 02:51:22


You could define your primary key as a unique clustered index with
IGNORE_DUP_KEY.  When you attempt the insert, if the record already exists,
you would get a benign warning.  You could then decide to perform the

Karl


> Hi,

> I am trying to write a stored procedure that inserts a record into a
table.
> If that record is already stored in that table, i just want to update it.
> However, I don't want to test whether that record exists before I do the
> insert (the reason being that 99% of the records will not exist in the db
at
> that time). In the case where the record exists, I want to trap the error
> message  that is raised and handle it internally within the stored
procedure
> and I don't want to return an error to the calling process.

> If I were to do this in an informix database i would have done something
> like:

> Begin
>     On Exception In (<error message for 'record exists'>)
>         Update Orders
>         Set Status = 4
>         Where orderid = 1;

>     End Exception

>     Insert Into Orders (1,2,3,4)
> End

> Thanks for your help.

> Ioannis Demetriades


 
 
 

Trapping and processing errors within stored procedures (SQL 7.0)

Post by Pat Phela » Wed, 14 Nov 2001 04:27:11


I'd suggest that you code it slightly differently, something like:

UPDATE myTable
   SET myCol = 'Updated'
   WHERE 1 = myTableId


   INSERT myTable (myCol) VALUES ('INSERTED')

This basically prevents the error, and does less overall I/O to boot!

-PatP

========================================

Please post DDL at the very least, and DML when possible to make it easier for people to help you.

Please reply only via newsgroups, as I rarely reply to email.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Trapping Errors Raised Within Stored Procedures

Does anyone know how to retrieve error information when an error has
been raised using RAISERROR in a stored procedure?
I am performing a Data Driven Query and the Insert is achieved by
calling a stored procedure.
Under certain conditions, the SP code raises an error (which results in
the step failing as intended).
The problem is that I can't find a way of obtaining the Error Code /
Description and thus pass suitable information on to the user.

Any ideas would be gratefully received.

John Hussey.

Sent via Deja.com
http://www.deja.com/

2. Licensing Question

3. Trapping Errors within a Stored Procedure

4. Strings vs. Numeric in Comparisons

5. How do i detect if tape is mounted with sql commands

6. trapping for deadlock errors within stored procedure?

7. Bookbiz database

8. How to trap errors within Stored Procs (SQL Server 6.5 SVCPK 3)

9. Trapping Return Codes within a Stored Procedure.

10. Trapping errors in 7.0 T-sql Stored Procs...

11. Calling Oracle Stored Procedure from within SQL Server Stored Procedure

12. Error Trapping within a procedure