Solved

Stored procedure error messages

Posted on 2000-02-29
13
620 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
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 …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now