Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

SQL SERVER AND ERROR MESSAGES

chprvkmrchprvkmr Posts: 31Member
Hello Friends,

I want to display my own error message when something goes wrong in executing the below procedure rather than displaying the sqlserver error message.

Suppose in the below procedure during updation if we set value of attribute, 'userid' which already exists in the database, an error willbe raised by sqlserver as 'userid' is primary key and duplicates are not allowed. Eventhough I incorporated a sql, 'select' statement whch intimates about error and rollback the transaction , sqlserver error message is displayed rather than the 'select' statement result which intimates me about the error encounterd. Can anyone of you suggest how to override the sqlserver error message and displaying my own error message and how could we get the sqlserver raised error description.

--------------

CREATE procedure admin_modify
@olduserid varchar(50),
@oldpassword varchar(50),
@newuserid varchar(50),
@newpassword varchar(50)
as

DECLARE @flag int
SET @flag = 0

BEGIN TRAN

select @flag = count(*) from admin where userid = @olduserid

IF @flag = 0
select 'failure' as result,'Admin ' + @olduserid + 'Not Found' as description
ELSE
BEGIN

UPDATE admin
set userid = @newuserid,password = @newpassword
where userid = @olduserid and password = @oldpassword

IF @@error<>0
BEGIN
ROLLBACK TRAN
select 'failure' as result,'Error Encountered While Modifying Admin ' + @olduserid + 'Details' as description
RETURN
END
ELSE
BEGIN
COMMIT TRAN
select 'success' as result,'' as description
END

END

-------------

regards,
Ch.Praveen Kumar.


Sign In or Register to comment.