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