Sunday, 19 October 2014

Custom Error Messages in Check Constraint Violation

Introduction

When a CHECK constraint of Table objects violates it gives an error message, showing that the check constraint is violated. One of the request that I get from a developers, that he want to provide the customized Error Message when the CHECK constraint violates.

Is it Possible?
In my point of view it is NOT possible. When the CHECK constraint violates it show the system error message like this.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_STDHIGHT_HIGHT". The conflict occurred in database
"PRACTICE_DB", table "dbo.tbl_PRODSTATUS".
The statement has been terminated.

So how can we customized it
We can use TRY,  CATCH and RAISERROR() to solve this problem. Here I am trying to demonstrate it.

Step-1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_PRODSTATUS', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_PRODSTATUS];
   END
GO
CREATE TABLE [dbo].[tbl_PRODSTATUS]
    (
      IDNO        INT  NOT NULL IDENTITY PRIMARY KEY,
      STDHIGHT    INT  NOT NULL,
      HIGHT       INT  NOT NULL
    );
GO

Step-2 [ Create CHECK Constraint ]

ALTER TABLE  [dbo].[tbl_PRODSTATUS]
ADD CONSTRAINT CHK_STDHIGHT_HIGHT
      CHECK(STDHIGHT>=HIGHT);

Step-3 [ Making Custom Error Message ]

--- Custom Error Handeling ---
EXEC sp_addmessage
     @msgnum   = 50020,
     @severity = 1,
     @msgtext  = 'Can not Insert records in Table : %s as %s can not >= columns %s';

If you need the reference of RAISERROR() function, please check my previous article.


Step-4 [ TRY,  CATCH and RAISERROR() to Solve ]

BEGIN
     BEGIN TRY
           DECLARE   @v_TblName   VARCHAR(50),
                     @v_ColName1  VARCHAR(50),
                     @v_ColName2  VARCHAR(50);

           SET @v_TblName= '[dbo].[tbl_PRODSTATUS]';
           SET @v_ColName1 ='[HIGHT]';
           SET @v_ColName2 ='[STDHIGHT]';  

           INSERT INTO  [dbo].[tbl_PRODSTATUS]
                (STDHIGHT, HIGHT)
           VALUES(30, 40);
     END TRY
     BEGIN CATCH
           RAISERROR(50020, 1, 1, @v_TblName, @v_ColName1, @v_ColName2);
     END CATCH
END

Step-5 [ Execute analyze the Output ]

(0 row(s) affected)
Can not Insert records in Table : [dbo].[tbl_PRODSTATUS] as [HIGHT] can not >= columns [STDHIGHT]
Msg 50020, Level 1, State 1



Hope you like it.




Posted by: MR. JOYDEEP DAS