Solved

Stored procedure error messages

Posted on 2000-02-29
13
626 Views
Last Modified: 2008-02-01
I'm using SQL Server 7.0.

When I'm writing stored procedures, I can't seem to trap errors when a command fails like when inserting a row, the key might already exist.  At this point, the stored procedure returns the error message.  How can I trap this inside the stored procedure, and replace the message with my own message?  Is there a way to handle this?
0
Comment
Question by:yurrea
13 Comments
 
LVL 3

Expert Comment

by:ahoor
ID: 2571978
You can check the error status, and if it's not zero you can print
a message. However the orginal message is always shown too.
0
 
LVL 4

Expert Comment

by:gcs001
ID: 2571996
Hi yurrea,
Here is some Error handling code you may want to try ...

Define these variables to store your error info in.
DECLARE @@ERROR_STOR int
DECLARE @@ERROR_STRING varchar(255)

After an Insert/Update, etc check the error code as follows:
Insert ....
SELECT @@ERROR_STOR = @@ERROR
IF (@@ERROR_STOR  <> 0)
BEGIN
   goto Error_Occurred
END

Error_Occurred:
SELECT @@ERROR_STRING = 'Error Code = ' + CONVERT (char(10), @@ERROR_STOR) + 'Occurred! '

IF (@@ERROR_STOR = 1)
BEGIN
  SELECT @@ERROR_STRING = @@ERROR_STRING + 'Problem Number 1!'
END
ELSE
IF (@@ERROR_STOR = 2)
BEGIN
  SELECT @@ERROR_STRING = @@ERROR_STRING + 'Problem Number 2!'
END
PRINT @@ERROR_STRING

HTH

- Grant.
0
 
LVL 15

Expert Comment

by:lyonst
ID: 2572286
Hi,

select @MyError = 0
select @MyError1 = 0
select @MyText = "0"

EXEC STATEMENT ...... Insert XXXXX

IF @@ERROR <> 0

select @MyError1 = @@error

if @MyError1 <> 0
   begin
     select @MyError = 1
     select @ErrorText=description
        from ....MessagesTable where error = @MYError1
   return
   end

This works for 6.5 and I assume it should work for 7.0 too.

If not let me know.

T.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:yurrea
ID: 2574866
qcs001:

I tried your code but to no avail, the error still shows:

Server: Msg 2627, Level 14, State 1, Line 0
Violation of PRIMARY KEY constraint 'PK_CustomerInformation'. Cannot insert duplicate key in object 'CustomerInformation'.
The statement has been terminated.
Error Code = 2627      Occurred!
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2575235
You can use a trigger to trap an error.  In the trigger test for the value of @@ERROR.  If it is 2627 then issue a ROLLBACK statement or you could create a custom error statement.

User-defined error messages can be added to the sysmessages table using the system stored procedure sp_addmessage. At a minimum, you can specify the message number, the severity level, and the message text using sp_addmessage.

Similar to specifying user-defined error messages with RAISERROR, use error message numbers greater than 50000 and severity levels from 0 through 18. Only system administrators can issue RAISERROR with a severity level from 19 through 25.

In this example you would have to add a custom message for message number 50009.  But, the second raiserror will post the string message.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'reminder' AND type = 'TR')
    DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS
IF @@ERROR = 2627
  ROLLBACK TRANSACTION
  -- raise a custom error or message:
  RAISERROR (50009, 16, 10)
IF @@ERROR > 0
  -- severity state 1 is for messages:
  RAISERROR ('An error occured while updating this table, so Im posting this message',1,1)



 
0
 

Author Comment

by:yurrea
ID: 2575562
Jon_Raymond:

The trigger doesn't work (or there's something wrong with the way I put it in place - don't worry, I'm still working on it!).

Will the trigger work even if a row cannot be inserted.

Suppose I have a table and I inserted a row which was rejected because of primary key constraint, is it possible to have custom (on the fly - without adding to sysmessages table) messages, instead of having the normal error messages?
0
 

Author Comment

by:yurrea
ID: 2575567
Jon_Raymond:

The trigger doesn't work (or there's something wrong with the way I put it in place - don't worry, I'm still working on it!).

Will the trigger work even if a row cannot be inserted.

Suppose I have a table and I inserted a row which was rejected because of primary key constraint, is it possible to have custom (on the fly - without adding to sysmessages table) messages, instead of having the normal error messages?
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2578821
I checked further and found that with this error the transaction gets rolled back before a trigger can fire.  So, you have to trap for the error in the stored procedure that does the update.  There's no "On Error" function that you can use.  You would have to manually check for the condition that would cause an error before you try the update, and then use raiserror to raise your user defined error.
0
 

Author Comment

by:yurrea
ID: 2579062
Jon Raymond:

I'm not that familiar with raiserror, but can I use it to send error messages (my own) without sending the original message?  And on-the-fly messages .. say for example .. when an error occurs, it will send 'error occured' without having to add a record in the sysmessages.  Similar to exception handling in Delphi (try-except).
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2582279
to send a message without adding to sysmessages use this syntax:

RAISERROR ('An error occured while updating this table, so Im posting this message',1,1)


The message is anything you want.  the last two numbers are severity level and state.  Using 1,1 is the lowest level and is equivalent to sending a message.  You can use higher severity levels and states.  Read up on this in Books online.  

You can't stop the system error messages, that I know of.  RAISERROR is for catching the error before the system does, so you can take some other action.

0
 

Author Comment

by:yurrea
ID: 2585990
Jon Raymond:

I tried again using your solution, to catch the error before the system does .. unfortunately, the system catches it first ..

Here's my code:

CREATE TABLE CustomerInformation
(
     @Name char(10) NOT NULL,
     @Age smallint NOT NULL
)

CREATE PROCEDURE sp_AddCustomer
     @Name char(10),
     @Age smallint
AS
BEGIN TRANSACTION
INSERT INTO CustomerInformation (Name, Age)
     VALUES (@Name, @Age)
IF @@ERROR > 0
BEGIN
     RAISERROR ('An error occured while updating this table, so Im posting this message',1,1)
     ROLLBACK TRANSACTION
END
ELSE
     COMMIT TRANSACTION


----------------------------------------
CALL
----------------------------------------
exec sp_AddCustomer 'Name', 2
select * from CustomerInformation

----------------------------------------
RESULT
----------------------------------------
Server: Msg 2627, Level 14, State 1, Line 0
Violation of PRIMARY KEY constraint 'PK_CustomerInformation'. Cannot insert duplicate key in object 'CustomerInformation'.
The statement has been terminated.
Msg 50000, Level 1, State 50000
An error occured.
Name       Age    AmountDue
---------- ------ ---------
Name       0      NULL
Name       1      NULL
Name       2      NULL

(3 row(s) affected)

0
 
LVL 6

Accepted Solution

by:
Jon_Raymond earned 100 total points
ID: 2586311
No, you have to check for the duplicate manually first to raise the error and then abort it yourself:

CREATE PROCEDURE dbo.sp_AddCustomer
     @Name varchar(10)='Dude',
     @Age smallint =1
AS

SELECT
      Name
FROM dbo.CustomerInformation
WHERE Name = @NAme

IF @@ROWCOUNT > 0
      BEGIN
      RAISERROR('Duplicate Primary Key Detected, Tranaction Aborted',1,1)
      END
ELSE
      BEGIN
            INSERT INTO CustomerInformation (Name, Age)
                 VALUES (@Name, @Age)
      END

0
 

Author Comment

by:yurrea
ID: 2586468
Jon Raymond:

OK, Thank You.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Download ms sql express. 2 25
sql server query 18 36
SQL Availablity Groups List 2 7
Need help in debugging a UDF results 7 21
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question