Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: sqlserver and errormessages

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you've hit the same problem you did in your other thread. Because SQL Server sees the messages as a stream, you can't control their appearance at the client. The client can filter out error messages that you don't want it to display, but that's the best that I know how to do!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •