sqlsql-serverstored-proceduressql-server-2012input-parameters

SQL - Limiting stored procedure input parameters


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

Solution

  • 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