Loading ...

How to bind rule to a column in SQL | CodeAsp.Net

How to bind rule to a column in SQL

 /5
0 (0votes)

In this blog I'm going to explain how we can bind some rule to a Column of a table in SQL. Many times we want that the column must contain the value in a particular format.

For Ex. Column Emp_ID should start with alphabet and after that it must contain 3 integers.

Like 'A123' or 'E456'.

This can be done in SQL by applying rules on the column.

1. Create a table

CREATE TABLE EMP

(

   EMP_ID NVARCHAR(20),

   EMP_NAME NVARCHAR(100)

)

2. Create a rule which you want for that column

Syntax is:
CREATE RULE RULE_EMP

AS@EMP_ID LIKE ('[A-Z][0-9][0-9][0-9]')

Now to bind the rule to a column, below is the syntax.

SP_BINDRULE  Rule_EMP,'EMP.EMP_ID' 

Now, try to insert a row in the column.

INSERT INTO EMP(EMP_ID,EMP_NAME) VALUES( '1234','VIJENDRA')

//ERROR
a column inserts or updates conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'TEST', table 'dbo.Emp', column 'EMP_ID'.

INSERT INTO EMP(EMP_ID,EMP_NAME) VALUES( 'A234','VIJENDRA')  //ROW INSERTED SUCCESSFULLY 

So there you are we have imposed restriction on the column EMP_ID that it can only take the value in a format mentioned above.

Do let me know your feedback or comments.

Comments (1)

   
skmurthy
Hi,Nice concept but that validation  we do in UI itself using  validation control or jscript.what is the need of here to bind rule with column? 
8/19/2010
 · 
by

Top Posts