SQL Server Error Messages - Msg 161

Error Message

Server: Msg 161, Level 15, State 1,
Procedure <Rule Name>, Line 1
Rule contains more than one variable.

Causes

Rules are a backward-compatibility feature that performs some of the same functions as CHECK constraints. When a rule is bound to a column or an alias data type, the rule specifies the acceptable values that can be inserted into that column.

The syntax of the CREATE RULE is as follows:

CREATE RULE <Rule Name>
AS <Condition Expression>

The <Condition Expression> is the condition or conditions that define the rule. One of the requirements of the <Condition Expression> is that it should include one and only one variable, which is preceded by the at sign (@). If the <Condition Expression> contains more than one local variable, this error message is raised.

Here’s an example of how the error can occur:

CREATE RULE [AddressRule]
AS @ZIPCode IS NOT NULL OR (@StateCode IS NOT NULL AND @City IS NOT NULL)

Msg 161, Level 15, State 1, Procedure AddressRule, Line 1
Rule contains more than one variable.

This rule tries to validate the address information by making sure that either the ZIP code is entered or both the State and City are entered.

Here’s another rule that generates this error:

CREATE RULE [DefaultPriceRule]
AS @DefaultPrice BETWEEN @MinimumPrice AND @MaximumPrice

Msg 161, Level 15, State 1, Procedure DefaultPriceRule, Line 1
Rule contains more than one variable.

This rule tries to make sure that the value set for the default price of a product is within the range of the minimum price and the maximum price.

CREATE RULE will be removed in a future version of Microsoft SQL Server. Avoid using CREATE RULE and instead make use of CHECK constraints instead. Using the CHECK constraint is the preferred, standard way to restrict the values in a column. There can be only one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.

Solution / Work Around

Depending on the requirement, a rule can either be replaced by a CHECK constraint or by a trigger. In this particular error message, since the rule involves more than one column from the table, the rule can be replaced with a CHECK constraint.

In the case of the first rule where it checks the ZIP code is not NULL or the State and City are not NULL, this rule can be replaced with the following CHECK constraint:

CREATE TABLE [dbo].[Customer] (
    [CustomerID]     INT NOT NULL IDENTITY(1, 1),
    [CustomerName]   VARCHAR(100),
    [Address]        VARCHAR(100),
    [City]           VARCHAR(50),
    [State]          CHAR(2),
    [ZIPCode]        VARCHAR(10)
) 
GO

ALTER TABLE [dbo].[Customer]  WITH CHECK 
ADD  CONSTRAINT [CK_Customer_AddressCheck] 
CHECK ( [ZIPCode] IS NOT NULL OR ([State] IS NOT NULL AND [City] IS NOT NULL))

If a new row is inserted to the table where either the ZIP code is NULL or the State is NULL and the City is NULL, an error will be raised:

INSERT INTO [dbo].[Customer] ( [CustomerName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Mickey Mouse', 'Disney World', NULL, 'FL', NULL )

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Customer_AddressCheck".
The conflict occurred in database "SQL2008", table "dbo.Customer".
The statement has been terminated.

In the case of the second rule which makes sure that the value set for the default price is within the range of the minimum price and the maximum price, this rule can be replaced with the following CHECK constraint:

CREATE TABLE [dbo].[Product] (
    [ProductID]         INT NOT NULL IDENTITY(1, 1),
    [ProductName]       VARCHAR(100),
    [DefaultPrice]      MONEY,
    [MinimumPrice]      MONEY,
    [MaximumPrice]      MONEY
)
GO

ALTER TABLE [dbo].[Product] WITH CHECK
ADD CONSTRAINT [CK_Product_DefaultPriceCheck]
CHECK ( [DefaultPrice] BETWEEN [MinimumPrice] AND [MaximumPrice] )
GO

If a new product is inserted into the table where the default price is not within the minimum price and the maximum price, an error will be generated by the CHECK constraint:

INSERT INTO [dbo].[Product] ( [ProductName], [DefaultPrice], [MinimumPrice], [MaximumPrice] )
VALUES ( 'Chocolate', $0.50, $1.00, $2.00) 

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Product_DefaultPriceCheck".
The conflict occurred in database "SQL2008", table "dbo.Product".
The statement has been terminated.