A. Rule with a range
This example creates a rule that restricts the range of integers inserted into the column(s) to which this rule is bound.
CREATE RULE range_rule
AS
@range >= $1000 AND @range < $20000
B. Rule with a list
This example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.
CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877')
C. Rule with a pattern
This example creates a rule to follow a pattern of any two characters followed by a hyphen, any number of characters (or no characters), and ending with an integer from 0 through 9.
CREATE RULE pattern_rule
AS
@value LIKE '_ _-%[0-9]'
Examples
This example unbinds and then drops the rule named pub_id_rule.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'pub_id_rule'
AND type = 'R')
BEGIN
EXEC sp_unbindrule 'publishers.pub_id'
DROP RULE pub_id_rule
END
GO
CREATE RULE id_chk AS @id BETWEEN 0 and 10000
GO
CREATE TABLE cust_sample
(
cust_id int
PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
)
GO
sp_bindrule id_chk, 'cust_sample.cust_id'
GO