in Database

Why should you use Rules in SQL Sever?

Validating data is one of the most important sections in data-driven applications. It’s a critical task to control and validate data that are inserting or updating. Most of developers are using constrains (Default, Check, Unique) in order to do so; most of them are using Check constrains in order to validate fields.
Sometimes, a developer needs to use a validation frequently. For example, imaging you’re going to validate email address in SQL Sever. In order to do so, you’ll need to write a complex regular expression as a check constraint. But there is one better way; you can use Rules to store a validation expression and then use it where ever you want.

Creating a rule is simple:

CREATE RULE EmailValidator
AS
begin

@value like '%@%.[a-z][a-z].[a-z][a-z]';

end

Once you create a rule, you can use frequently. In order to do so, you need to make use of sp_bindrule system stored-procedure.

The structure of sp_bindrule property is something like this:

sp_bindrule [ @rulename = ] 'rule' , 
[ @objname = ] 'object_name' 
[ , [ @futureonly = ] 'futureonly_flag' ]

And here’s an example. Note that today is the name of our rule:

USE master;
GO
EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate'

For more information about sp_bindrule you can out its MSDN article: http://msdn.microsoft.com/en-us/library/ms176063/