• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Stored procedure error messages

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
yurrea
Asked:
yurrea
1 Solution
 
ahoorCommented:
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
 
gcs001Commented:
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
 
lyonstCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
yurreaAuthor Commented:
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
 
Jon_RaymondCommented:
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
 
yurreaAuthor Commented:
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
 
yurreaAuthor Commented:
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
 
Jon_RaymondCommented:
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
 
yurreaAuthor Commented:
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
 
Jon_RaymondCommented:
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
 
yurreaAuthor Commented:
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
 
Jon_RaymondCommented:
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
 
yurreaAuthor Commented:
Jon Raymond:

OK, Thank You.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now