UDF within CHECK Constraint

Here I am trying to demonstrate the uses of User define Function (UDF) within CHECK Constraint.

For this I am taken an example case study. Here we have a table named T_STOCK. It contains columns like.

IDNO	INT	Primary Key
RATE	DECIMAL(20,2)
AGREEDRATE	DECIMAL(20,2)

 The CHECK Constraint must check the AGREEDRATE< RATE. But in the business logic for some free sample the RATE should be Zero. If this constraint presents the Insert statement not allows inserting records. To get this Insert we are using a function named fnCHECKRATE. It checks that if the RATE is zero it allows the Insert records and if REATE is > 0 than it check with AGREEDRATE by this condition AGREEDRATE< RATE.

-- Craeting the Base Table --

IF OBJECT_ID('T_STCOK') IS NOT NULL

   BEGIN

     DROP TABLE T_STCOK;

   END

GO

CREATE TABLE T_STCOK

       (IDNO       INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,

        RATE       DECIMAL(20,2) NOT NULL,

        AGREEDRATE DECIMAL(20,2) NOT NULL);

       

GO

-- Creating Function for CHECK constraint --

IF OBJECT_ID('fnCHECKRATE') IS NOT NULL

   BEGIN

      DROP FUNCTION dbo.fnCHECKRATE;

   END

GO  

CREATE FUNCTION dbo.fnCHECKRATE

(

     @v_RATE       DECIMAL(20,2),

     @v_AGREEDRATE DECIMAL(20,2)

)

RETURNS BIT

AS

BEGIN

     DECLARE @v_RETVAL BIT;

     SET @v_RETVAL = 0;

    

     IF @v_RATE <=0

        BEGIN

           SET @v_RETVAL = 1;

        END

     ELSE

        BEGIN

           IF @v_AGREEDRATE < @v_RATE

              BEGIN

                 SET @v_RETVAL = 1;

              END

           ELSE

              BEGIN

                 SET @v_RETVAL = 0;

              END  

        END

       

     RETURN @v_RETVAL;

END

GO

-- Create Constraint with Function Call --

IF NOT EXISTS(SELECT *

              FROM   sys.sysconstraints

              WHERE  id = OBJECT_ID('T_STCOK')

                     AND constid = OBJECT_ID('cons_Chk_RATE'))

     BEGIN

           ALTER TABLE T_STCOK

           ADD CONSTRAINT cons_Chk_RATE CHECK (dbo.fnCHECKRATE(ISNULL(RATE,0), ISNULL(AGREEDRATE,0))=1)

    END

GO

-- Inserting Records --

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (20, 5);

(1 row(s) affected) 

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (5, 20); 

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "cons_Chk_RATE". The conflict occurred in database "ATS", table "dbo.T_STCOK".

The statement has been terminated.

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (0, 5);

 

(1 row(s) affected)      

Hope you like it.

Posted by: MR. JOYDEEP DAS