Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected



Here is what the simplified code looked like

CREATE PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
AS
SELECT @SomeOtherParam
GO

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1

EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default

ALTER PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
AS
SELECT @SomeThirdParam
GO

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used