Блог

Главная - Вопросы по программированию - Создайте белый список значений, которые будут использоваться в значении параметра хранимой процедуры

Создайте белый список значений, которые будут использоваться в значении параметра хранимой процедуры

#sql-server #tsql #stored-procedures #parameters #constraints

Вопрос:

У меня есть хранимая процедура, которая принимает строковый параметр:

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

Однако не все строковые значения являются допустимыми значениями для этого параметра. Я знаю, что смогу это сделать:

 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

Но это решение одновременно трудно читается в зависимости от того, где именно я могу поставить проверку (какие другие проверки необходимо выполнить и т.д.), И зависит от пользовательских номеров ошибок, которых я хотел бы избежать (стандартизация не всегда лучше, но в большинстве случаев это так).

Еще одно решение, которое я нашел, состоит в том, чтобы создать пользовательский тип данных и привязать к нему правило:

 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

Но это решение является и тем, и другим
а) устарел
б) работает не так, как ожидалось:

 -- 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

Я хотел бы сделать что-то подобное:

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

Но не смог найти для этого правильного синтаксиса. SQL Server вообще поддерживает эту функцию?

Ответ №1:

Метод, который вы используете в своем первом примере, является правильным. Однако что вы могли бы сделать, так это создать таблицу с принятыми значениями внутри. Тогда вы могли бы использовать EXISTS вместо этого, чтобы проверить, действительно ли значение в таблице. Это сделало бы вашу процедуру более краткой, и если вам нужно выполнить проверку в нескольких процедурах, это облегчает ее передачу, а добавление значения в таблицу проверки «обновляет» все процедуры.

Итак, очень простыми словами:

 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
Метки: ,