yurrea
asked on
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?
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?
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.
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.
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.
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.
ASKER
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!
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!
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)
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)
ASKER
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?
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?
ASKER
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?
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?
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.
ASKER
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).
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).
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.
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.
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jon Raymond:
OK, Thank You.
OK, Thank You.
a message. However the orginal message is always shown too.