Solved

Stored procedure error messages

Posted on 2000-02-29
13
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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