I want to display Custom Error Message!!

Question

I want to display Custom Error Message!!

Oracle_91

SSChampion

Points: 14548
More actions
January 31, 2010 at 6:06 am

#100057

Hi,
Am using a sample stored procedure to handle transactions.
Whenever any error , it should display the message in the RAISERROR MSG. But the control is not going there.
How can i display my own Error msg??
Code:
-------
create table sample
(id int,
name varchar(20),
sal numeric(18,2)
)
insert into sample
select 101,'manu',8995.66
union all
select 102,'anu',5767.56
CREATE PROC USP_Sample
AS
BEGIN
BEGIN TRAN
INSERT into sample values(103,'zyz',8098)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10 /* Any non-zero value */
END
print '-- 1 row inserted successfullly --'
UPDATE sample
SET SAL = '***' -- ERR!!! "SAL" is numeric (18,2)
WHERE id = 102
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('!!INSERT success but UPDATE failed so ENTIRE TRAN Rollbacked:INVALID datatype!!.', 16, 1)
RETURN 11 /* Any non-zero value */
END
print '-- 1 row updated successfully --'
COMMIT TRAN
END
exec USP_Sample
/* Output
Server: Msg 8114, Level 16, State 5, Procedure USP_Sample, Line 17
Error converting data type varchar to numeric.
*/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in sql 2000, you cannot do it.

any error 16 or above, which would be datatype error like you saw, constraint violations for foreign keys/pk/unique constraints cannot be custom captured.

you can do it if you upgrade to sql 2005 and above and use a try..catch block.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

Oracle_91

SSChampion

Points: 14548

January 31, 2010 at 1:12 pm

#1111359

Thanks for the help.

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi Mahesh,

But, you can raise this message by you validate the string before updating, is it valid data type to update the target.

if isnumeric('***')= 0

begin

ROLLBACK TRAN

RAISERROR ('!!INSERT success but UPDATE may fail :INVALID datatype!!.', 16, 1)

RETURN 11 /* Any non-zero value */

END

UPDATE sample

SET SAL = '***' -- ERR!!! "SAL" is numeric (18,2)

WHERE id = 102

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Thanks Arun.

Its been long time i heard from you 🙂