July 29, 2019 by Kenneth Fisher
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.
EXEC LotsOfParams 1, 2, 3; EXEC LotsOfParams 1, 2, 3, 4; EXEC LotsOfParams @Param1 = 1, @Param3 = 2, @Param2 = 3;
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