Want to display myown Error Msg!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Want to display myown Error Msg!!

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-31 : 08:02:45

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.

*/

martinch
Starting Member

35 Posts

Posted - 2010-02-03 : 04:24:18

Hi Frank,

The issue you have is that the error is occurring in the statement "UPDATE Sample SET SAL = '***'...", and it's of a high-enough severity to stop execution, instead of letting it follow through - therefore it doesn't reach the statement that checks if there was an error and throws a different message ("IF @@Error <> 0").

Instead of using "IF @@Error <> 0", you need to use "TRY" and "CATCH" (see Books Online for more info).

Hope this helps. :)