Wednesday, July 2, 2014

Stored Procedure Parameter Validation

This procedure isn't supposed to do anything. It's merely here as a reminder to validate your input parameters!
Here I'm using RAISERROR to return error code 18 and terminate the stored procedure...
18 is a Nonfatal Internal Error that allows you to return the message to the log.

CREATE PROCEDURE [tools].[dosomething]
(
     @databasename  varchar(200) 
    ,@recoverymodel             varchar(11)
 
)
AS

BEGIN

IF @recoverymodel NOT IN ('FULL','SIMPLE','BULK_LOGGED')
 BEGIN
     RAISERROR('Invalid parameter: @recoverymodel should be FULL, SIMPLE or BULK_LOGGED', 18, 0)
  RETURN
 END

-- Do stuff here...

END

No comments: