How to call a user defined function in Check Constraint on table

We can call user defined function in a constraint. This is very helpful when we have to add similar kind of constraint on many tables.

Let’s take an example here

Create a function

CRAETE FUNCTION [TestFucntion] (@Salary nvarchar(50))

RETURNS BIT

AS

BEGIN

DECLARE @Exists BIT

      IF(@Salary >1500)

            BEGIN

            SET @Exists =0

            END

      ELSE

            BEGIN

            SET @Exists =1

            END

RETURN @Exists

END

Execute the above script and it will create a function which can be seen in object explorer of the selected database, under the function folder.

Create a table employee

Create table Employee (ID int, Salary money)

Add a testConstraint on table employee

GO

Alter TABLE Employee

WITH NOCHECK ADD  CONSTRAINT [testConstraint]

CHECK(([dbo].[ TestFucntion]([Salary])=(0)))

GO

The above script will add constraint on the table Employee and will not allow entering salary more than 1500.

Thus we can do any other activity here in function and can write complex constraint for table easily.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s