Trapping Errors in Stored Procedures

Trapping Errors in Stored Procedures

Post by Kevin Whit » Sat, 10 Apr 1999 04:00:00



Hello,

I have a stored procedure that returns an error message when an error
occurrs such as
an unique key violation but the message seems to be generated by the db
engine.  My return value is updated correctly.  Can I suppress the
"Violation of PRIMARY KEY constraint" msg?  This can happen because a web
user can hit the back button on the browser and re-submit the insertion.  I
was trying to get away with any duplicate value checking logic

Can anybody help with suppressing  the "Violation of PRIMARY KEY constraint"
msg?

Thanks in advance.

I am using SQL Server 7.0 on Windows NT server ( with sp4 ).

Here is a snippet of the stored procedure:
CREATE PROCEDURE [sp_test]
as




set nocount on

insert into tbl_detail( d_id, item_id, status, last_update )
values ( 32, 1, 'A', getdate() );



 begin

  print "error"
 end


 
 
 

Trapping Errors in Stored Procedures

Post by Yan Y » Sat, 10 Apr 1999 04:00:00


Kevin,

Try following (replace <YourSpecificErrorNumberHere> with your specific
integer value). Procedure assumes d_id being primary key.

CREATE PROCEDURE [sp_test]
as





set nocount on

IF NOT EXISTS (SELECT 1 FROM tbl_detail WHERE d_id = 32)
  BEGIN
 insert into tbl_detail( d_id, item_id, status, last_update )
 values ( 32, 1, 'A', getdate() );
  END
ELSE
  BEGIN

  END



violation error code
 begin

  print "error"
 end


Yan.


> Hello,

> I have a stored procedure that returns an error message when an error
> occurrs such as
> an unique key violation but the message seems to be generated by the db
> engine.  My return value is updated correctly.  Can I suppress the
> "Violation of PRIMARY KEY constraint" msg?  This can happen because a web
> user can hit the back button on the browser and re-submit the insertion.
I
> was trying to get away with any duplicate value checking logic

> Can anybody help with suppressing  the "Violation of PRIMARY KEY
constraint"
> msg?

> Thanks in advance.

> I am using SQL Server 7.0 on Windows NT server ( with sp4 ).

> Here is a snippet of the stored procedure:
> CREATE PROCEDURE [sp_test]
> as




> set nocount on

> insert into tbl_detail( d_id, item_id, status, last_update )
> values ( 32, 1, 'A', getdate() );



code
>  begin

>   print "error"
>  end