Optional vs Required parameters in Stored Procedures


If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.

CREATE PROCEDURE LotsOfParams (@Param1 INT, @Param2 INT, @Param3 INT, 
			@Param4 INT=NULL, @Param5 INT=0, @Param6 INT=5)

The first three parameters are required and the next three aren’t. You’ll notice that any valid value is fine even NULL.

These work:

EXEC LotsOfParams 1, 2, 3;
EXEC LotsOfParams 1, 2, 3, 4;
EXEC LotsOfParams @Param1 = 1, @Param3 = 2, @Param2 = 3;

These don’t:

EXEC LotsOfParams 1, 2;
EXEC LotsOfParams @Param4 = 1, @Param5 = 2;
EXEC LotsOfParams 1, @Param4 = 1, @Param5 = 2;

A couple of things of note here.

  • If you don’t specify the parameter names then they are strictly in the defined order. (@Param1, @Param2, etc)
  • If you do specify parameter names then it doesn’t matter what order you put them in.
  • Regardless of what order you put the parameters in, and if they are named or not, all of the required parameters must be there.
  • This doesn’t seem to be the case for function calls. As far as I can tell there is no way to make a function parameter optional.
  • What do I do if I want a default for the parameter but I still want it to be required? Go the old fasioned route.
    ALTER PROCEDURE LotsOfParams (@Param1 INT, @Param2 INT, @Param3 INT, 
    			@Param4 INT=0, @Param5 INT=0, @Param6 INT=0)
    IF @Param4 IS NULL
    	SET @Param4 = 5;
    IF @Param5 IS NULL
    	SET @Param5 = 12;
    PRINT 1;

Original post (opens in new tab)
View comments in original post (opens in new tab)