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)
AS
PRINT 1;
GO

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)
  AS
  IF @Param4 IS NULL
  SET @Param4 = 5;
  IF @Param5 IS NULL
  SET @Param5 = 12;
  PRINT 1;
  GO