Monday 25 March 2013

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

1 comment:

  1. PLEASE DO NOT RECOMMEND OR DO THIS!!

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/25/scalar-udfs-wrapped-in-check-constraints-are-very-slow-and-may-fail-for-multirow-updates.aspx

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/02/07/using-a-udf-in-a-check-constraint-to-check-validity-of-history-windows-start-end-date-windows.aspx

    ReplyDelete