-2

I have a table Person with a column SSN . I want to use a rule to make sure the SSN items entered are syntactically- correct, i.e., they consist of strings of the form xxx-xx-xxxx where each x is a positive integer in {0,1,2,..,9}

Person  ( PersonID Primary Key NOT NULL, 
 Int, SSN  VarChar(11), FName  Char(50), LName Char(50)) 

and ultimately bind the rule

CREATE Rule SSN Validation
AS 
@Variable LIKE  ‘[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]‘

I then bind it:

Exec Sp_bindrule

Am I on the right track?

I also wanted to exclude a few values from the list. I don't have an idea on how to do it.

-1

I first want you to know that rules are deprecated with SQL Server. A better solution would be to have a function to test the value or a stored procedure that handles the insert and also tests the value.

A function could look like this:

CREATE FUNCTION dbo.IsValidSSN
(
    @value VARCHAR(11)
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT;
    IF (@value LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
        SET @result = 1;
    ELSE
        SET @result = 0;

    RETURN @result;
END;

GO

To test it:

DECLARE @ssn VARCHAR(11) = '012-34-5678'

SELECT dbo.IsValidSSN(@ssn)

SELECT @ssn = 'A12-34-5678'

SELECT dbo.IsValidSSN(@ssn)

After adding the function you can add a CHECK constraint for that column:

ALTER TABLE dbo.Person ADD CONSTRAINT ssnCheck CHECK (dbo.IsValidSSN(SSN) = 1) 

To test it

INSERT INTO dbo.Person
(
    PersonID,
    SSN,
    FName,
    LName
)
VALUES
(   1,  -- PersonID - int
    '111-22-3333', -- SSN - varchar(11)
    'A', -- FName - char(50)
    'B'  -- LName - char(50)
    )

INSERT INTO dbo.Person
(
    PersonID,
    SSN,
    FName,
    LName
)
VALUES
(   2,  -- PersonID - int
    'Z11-22-3333', -- SSN - varchar(11)
    'AA', -- FName - char(50)
    'BB'  -- LName - char(50)
    )

The result:

Msg 547, Level 16, State 0, Line 22 The INSERT statement conflicted with the CHECK constraint "ssnCheck". The conflict occurred in database "DB1", table "dbo.Person", column 'SSN'. The statement has been terminated.

If you're still willing to go with rules, this what it would look something like:

CREATE RULE SSN_Validation
AS
@Variable LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]';
GO

EXEC sys.sp_bindrule @rulename = N'SSN', @objname = N'dbo.Person.SSN';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.