Optional vs Required parameters in Stored Procedures
13July 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.
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
SQL Studies 
Dumb question that I can’t be bothered to check for myself at the moment,,,
Do either of these work ?
EXEC LotsOfParams 1, @Param2 = 2, @Param3 = 3;
EXEC LotsOfParams @Param1 = 1, @Param2 = 2, 3;
First one works, second one doesn’t. Once you start using the param names you have to continue 🙂
[…] Kenneth Fisher takes us through procedure parameters: […]
If a default parameter value is defined in the stored procedure, then simply use the DEFAULT keyword for said parameter in the EXEC statement. It is a lot cleaner than a bunch of IF NULL statements and is better for documentation.
CREATE PROCEDURE LotsOfParams (@p1 int = 0, @p2 int = 0, @p3 int = 0)
AS
SET NOCOUNT ON;
SELECT @p1 as [p1], @p2 as [p2], @p3 as [p3];
GO
If defaults are assigned to all parameters then you can run the proc by itself:
EXEC LotsOfParams
And if you need to modify just one parameter, you can supply just the one:
EXEC LotsOfParams @p2 = 99
But it is best to always supply all the parameters IMO to make the code easier to read:
EXEC LotsOfParams @p1 = default, @p2 = 99, @p3 = default
I disagree. Just as an example what if you have 20 parameters? That could get really ugly. Honestly even with 2 or 3 parameters it doesn’t look that clean. And when you are reading the code “default” doesn’t mean a whole lot to anyone. Most people realize if you haven’t passed in a parameter you’ve accepted the default.
Don’t recall I’ve ever created a proc with 20 parameters other than upserts which would have few if any default parameters, so I guess I wouldn’t want to pass DEFAULT that many times either. A function with that many parameters would require a value or the keyword DEFAULT for each position as you noted.
For your last bullet point, parameter checking like in PowerShell to prevent NULL, restrict to a range of values, etc. would save a lot of IF statements. Even the same options for default value or binding on a column would be an improvement–and using a custom function would provide a lot of flexibility (but probably wreak havoc on plan reuse).
I was specifically thinking of things like sp_WhoIsActive (24), sp_DBPermissions (12), sp_BlitzIndex (9). Utility functions mostly.
I have to admit it would be nice, although in the end I don’t mind putting in code to test each parameter if needed. I can put in custom error handling if I want, put in default values based on other values (if needed, etc).
All of that said, I have objections to putting the DEFAULT keyword in when you call a SP, I just find it kind of cumbersome.
[…] that I couldn’t assign default values to parameters, which meant that I couldn’t have optional parameters. In the end I compromised. sp_AzSYNDBPermissions doesn’t have any parameters. It runs the […]
A stored procedure has three input parameters: @Param1, @Param2, @Param3. All are integer data type
* If @Param1 is not null, then return @Param1.
* If @Param1 is null and @Param2 is not null, then return @Param2.
* If @Param1 is null and @Param2 is null and @Param3 is not null, then return @Param3.
* If all three parameters are null, then return the value 0.
You need to write an expression that returns an integer by implementing the business logic.
Which expression should you write?
I’d layer ISNULLs honestly. ISNULL(@Param1, ISNULL(@Param2, ISNULL(@Param3, 0)))
COALESCE(@param1,@Param2,@Param3,0)
When optional parameters are defined do they all have to be at the end of the parameters list or some can be at the beginning followed by non-optional parameters?
You can define them like that but you still have to include all of the non-optional parameters, and if there are any parameters (optional or not) before them you’ll still have to name them all or include the optional ones.