Allow stored procedure to accept the values-CodePudding

I new to SQL, and I need one parameter called @User1 in the stored procedure to accept just 3 words for example ('max', 'low', 'high'), otherwise it should be an error.

I try to type this but it doesn't work I mean it accept any string value of data and I need to just 3 words:

IF @User1 <> 'max' OR @User1 <> 'low' OR @User1 <> 'high'
BEGIN
    SET @ErrorNumber = 3
    RETURN 0 
END

CodePudding user response：

You need an AND instead of an OR between your conditions. As from OP's last comment, to also make it case insensitive:

IF  LOWER(@User1) <> 'max' and LOWER(@User1) <> 'low' and LOWER(@User1) <>'high'
BEGIN 
    SET @ErrorNumber = 3
    RETURN 0 
END

Or simply

IF  LOWER(@User1) NOT IN ('max', 'low', 'high')
BEGIN 
    SET @ErrorNumber = 3
    RETURN 0 
END

CodePudding user response：

I think you are trying to create a stored procedure that accepts one parameter and you want to check that the parameter is in [max, low, high].

Check this

CREATE PROCEDURE UserSize @User1 nvarchar(30)
AS
BEGIN
    IF  @User1<>'max'  or @User1<>'low' or @User1<>'high'
    BEGIN
          THROW 51000, 'The record does not exist.', 1;  
    END

//do something here

END
GO;

Does this work for you?