Create a whitelist of values to be used in a stored procedure parameter value

Question

I have a stored procedure which takes a string parameter:

CREATE PROCEDURE sp_my_procedure
  @param1 NVARCHAR(100)
AS
BEGIN
  -- do stuff
END

However, not all string values are valid values for this parameter. I know I can do this:

CREATE PROCEDURE sp_my_procedure
  @param1 NVARCHAR(100)
AS
BEGIN
  IF @param1 NOT IN ('val1', 'val2', ...)
  BEGIN
    -- raise error and then return
  END
  
  -- do stuff
END

But this solution is both hard to read depending on where exactly I can put the check (what other checks need to be done, etc.) and relies on custom error numbers, which I would like to avoid (standardised is not always better, but most of the time it is).

Another solution I found is to create a user-defined data type and bind a rule to it:

CREATE RULE my_rule
AS
@val IN ('val1', 'val2', ...);
GO

CREATE TYPE RESTRICTED_STRING_TYPE
FROM NVARCHAR(100) NOT NULL;
GO

EXEC sp_bindrule 'my_rule', 'RESTRICTED_STRING_TYPE';
GO

But this solution is both
a) deprecated
b) not working as expected:

-- after type and rule has been created

DECLARE @val RESTRICTED_STRING_TYPE; -- this should fail because of the not null clause
SET @val = 'invalid value'; -- but even if not null is not there, this should definitely fail

PRINT(@val); -- nope, 'invalid value' is printed without error

I would like to do something like this:

CREATE PROCEDURE sp_my_procedure
  @param1 NVARCHAR(100) IN ('val1', 'val2', ...)
AS
BEGIN
  -- do stuff
END

But couldn't find the right syntax for it. Does SQL Server even support this functionality?

Answer 1

The method you use in your first example is the right one. What you could do, however, is create a table with the accepted values in there. Then you could use an EXISTS to check the value is valid against the table instead. This would make your procedure more succinct, and if you need to do the implement the validation in multiple procedures, it makes it more easily transferable, and adding a value to your validation table "updates" all the procedures.

So, in very simple terms:

CREATE TABLE dbo.RESTRICTED_STRING_VALUES (StringValue nvarchar(100);
INSERT INTO dbo.RESTRICTED_STRING_VALUES
VALUES (N'Val1'),
       (N'Val2'),
       (N'Val3');
GO
CREATE PROCEDURE dbo.my_procedure --Don't use "sp_" as a prefix! It's reserved by Microsoft.
  @param1 NVARCHAR(100)           --Doing so comes at a performance cost,
                                  --and could result in the Proc simply not working after an update
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM dbo.RESTRICTED_STRING_VALUES WHERE StringValue = @param1)    
    BEGIN
        --Raise error and then return
    END;
  
    --do stuff
END;
GO