SQL - Limiting stored procedure input parameters

Question

I have created this stored procedure that accepts the parameter @UpdType char(1). The procedure executes no matter what parameter is entered. However; the stored procedure only completes its function if the parameter is either R or U.

Is the there a way to constrain the stored procedure to only accept the parameters R or U as inputs?.

CREATE PROCEDURE UPDATECATS @UPDTYPE CHAR(1)
AS
BEGIN
    IF @UPDTYPE = 'U' --UPDATE TO NEW CATEGORIES IF PARAMETER IS R
    BEGIN
        UPDATE CATLEVEL1 
        SET CAT1DESC = 'LABORATORY EQUIPMENT' 
        WHERE CAT1DESC = 'MEDICAL & ALLIED EQUIPMENT'

        UPDATE CATLEVEL1 
        SET CAT1DESC = 'MACHINERY & CAMPUS EQUIPMENT' 
        WHERE CAT1DESC = 'OTHER MACHINERY & EQUIPMENT'
    END

    IF @UPDTYPE = 'R' --REVERSE TO OLD CATEGORIES IF THE PARAMETER IS U
    BEGIN
        UPDATE CATLEVEL1 
        SET CAT1DESC = 'MEDICAL & ALLIED EQUIPMENT' 
        WHERE CAT1DESC = 'LABORATORY EQUIPMENT'

        UPDATE CATLEVEL1 
        SET CAT1DESC = 'OTHER MACHINERY & EQUIPMENT' 
        WHERE CAT1DESC = 'MACHINERY & CAMPUS EQUIPMENT'
    END

    IF @UPDTYPE = 'R' OR @UPDTYPE = 'U' --UPDATE THE CATEGORIES IN THE ASSETS TABLE
    BEGIN
        UPDATE ASSETS 
        SET ASSETS.CAT3 = B.CATLEVEL3LINK 
        FROM ASSETS A 
        LEFT JOIN ASSETDESC B ON A.ASDES = B.ASSETDESCRIPTION

        UPDATE ASSETS 
        SET ASSETS.CAT2 = B.CATLEVEL2LINK 
        FROM ASSETS A 
        LEFT JOIN CATLEVEL3 B ON A.CAT3 = B.CAT3DESC

        UPDATE ASSETS 
        SET ASSETS.CAT1 = B.CATLEVEL1LINK 
        FROM ASSETS A 
        LEFT JOIN CATLEVEL2 B ON A.CAT2 = B.CAT2DESC
    END
END

score 4 · Accepted Answer · answered Mar 16 '16 at 18:33

One way is to add simple validation:

CREATE PROCEDURE UPDATECATS @UPDTYPE CHAR(1)
AS
BEGIN
IF @UPDTYPE IN ('R', 'U')
  BEGIN
    -- your code
  END
ELSE
  BEGIN
    -- RAISERROR/THROW
  END
END

or:

CREATE PROCEDURE UPDATECATS @UPDTYPE CHAR(1)
AS
BEGIN
  IF (@UPDTYPE NOT IN ('R', 'U')) OR (@UPDTYPE IS NULL)
    RAISERROR/THROW ..

  --rest your code
END

score 2 · Answer 2 · answered Mar 16 '16 at 18:35

2

You have some options.

Option 1: Return a status from the stored procedure, and check valid values on input. In other words, something like this:

if (@UPDTYPE NOT IN ('R', 'D'))
begin
    print 'Invalid argument';
    return(-1);
end;

Option 2: Use flags instead IsRUpd. You can declare this to be bit not null to limit it to two values.

Option 3: Create your own user-defined type that only accepts these two values.

In my opinion, the first method is the easiest method to implement. And, proper error handling in stored procedures has many longer term benefits.

answered Mar 16 '16 at 18:35

Gordon Linoff

1,242,037
58
646
786

`NOT INT`. You should handle `NULL` [Demo](https://data.stackexchange.com/stackoverflow/query/451568) – Lukasz Szozda Mar 16 '16 at 18:38
User defined types have known maintainability issues, so taking into account that there are only two values, using non nullable bit, like IsRead is much better. Using bit is better than having validation, since you have a cleaner and well defined contract for your stored procedure - and any invalid value will be detected at the moment of interpretation, not running. – ironstone13 Mar 16 '16 at 18:50

SQL - Limiting stored procedure input parameters

2 Answers2