locked
Raising Custom Error Message RRS feed

  • Question

  • Hi,

    My requirement is to display an custom error message.

    Actually i created a check constraint on  a column and when it gets violated i have to display some custom error message. And this should be achieved with out overriding the messages in sys.messages table.

    can some one suggest me whether i can achieve this or not. if yes how?

    thanks in advance

    kishor

    Tuesday, November 20, 2012 8:38 AM

Answers

  • Apparently no one bothers to read your entire post and all decide to refer you to the same generic capability.  What you ask cannot be done.  You cannot define a custom message for your check constraint.  If you must use a custom message, you could implement the constraint in a trigger. 
    • Proposed as answer by Naomi N Wednesday, November 21, 2012 3:14 AM
    • Marked as answer by Iric Wen Monday, December 3, 2012 6:38 AM
    Tuesday, November 20, 2012 2:14 PM

All replies

  • use raiseerror

    http://msdn.microsoft.com/en-us/library/ms177497(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, November 20, 2012 8:41 AM
  • Read this

    http://msdn.microsoft.com/en-us/library/ms178592.aspx

    Use Raiserror please. See this example

    BEGIN TRY
        -- RAISERROR with severity 11-19 will cause execution to 
        -- jump to the CATCH block.
        RAISERROR ('Error raised in TRY block.', -- Message text.
                   16, -- Severity.
                   1 -- State.
                   );
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
    
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    
        -- Use RAISERROR inside the CATCH block to return error
        -- information about the original error that caused
        -- execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    END CATCH;


    Many Thanks & Best Regards, Hua Min




    Tuesday, November 20, 2012 8:45 AM
  • Hi,

    Go through this articles,it will be helpfull to you

    http://beyondrelational.com/modules/2/blogs/94/posts/11991/raising-custom-messages-through-raiserror.aspx

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 20, 2012 8:52 AM
  • Use RaiseError Function to display Custom Error Messages.

    Here is an example


    RAISERROR
        (N'U can Write the Custom Error Message',
        10, -- Severity.
        1 -- State.
        );
    GO

    RAISERROR
        (N'U can Write the Custom Error Message',
        15, -- Severity.
        1 -- State.
        );
    GO


    BEGIN TRY
        RAISERROR ('Custom Message to Display Raised in TRY Block.', -- Message text.
                   16, -- Severity.
                   1 -- State.
                   );
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE(),
               ERROR_SEVERITY(),
               ERROR_STATE();
    ----Also Can Raise the Same Error Even In CATCH Block 
       RAISERROR ('Custom Message to Display Raised in TRY Block can be raised in CATCH Block also.', -- Message text.
                   16, -- Severity.
                   1 -- State.
                   );
    END CATCH;


    Please have look on the comment

    Tuesday, November 20, 2012 8:54 AM
  • You can use RAISEERROR..

    Example : Simple TRY…CATCH with RAISEERROR function
    
    BEGIN TRY
    DECLARE @MyInt INT;
    -- Following statement will create Devide by Zero Error
    SET @MyInt = 1/0;
    END TRY
    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    SELECT @ErrorMessage = ERROR_MESSAGE();
    RAISERROR (@ErrorMessage, 16, 1);
    END CATCH;
    GO


    Best Regards, Venkat

    Tuesday, November 20, 2012 8:55 AM
  • Hi ,

    Please see below link hope you can :

    http://www.techrepublic.com/blog/datacenter/define-custom-error-messages-in-sql-server-2005/390

    or

    http://dataeducation.com/blog/sql-servers-raiserror-function


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Tuesday, November 20, 2012 11:28 AM
  • Apparently no one bothers to read your entire post and all decide to refer you to the same generic capability.  What you ask cannot be done.  You cannot define a custom message for your check constraint.  If you must use a custom message, you could implement the constraint in a trigger. 
    • Proposed as answer by Naomi N Wednesday, November 21, 2012 3:14 AM
    • Marked as answer by Iric Wen Monday, December 3, 2012 6:38 AM
    Tuesday, November 20, 2012 2:14 PM