Stored Procedure argument "NULL" or "= NULL"

Question

What is the difference between:

CREATE PROCEDURE [dbo].[MyProcedure] 
                @MyArgument INT NULL

and

CREATE PROCEDURE [dbo].[MyProcedure] 
                @MyArgument INT = NULL

I used the first one, and it worked fine in SQL Server 2016. But SQL Server 2012 did not accept it. Both works on SQL Server 2016, and I am using the second one now without problem. But it would be interesting to know the difference.

Thanks!

Answer 1

They don't do the same thing. The second one defines a default value for the case that the caller doesn't specify one. The first one doesn't.

The "Transact-SQL Syntax for Natively Compiled Stored Procedures" grammar allows parameter datatypes to be declared as allowing NULL or NOT NULL. This was introduced for Hekaton (memory optimised tables).

Though it isn't documented as supported for the grammar in "Transact-SQL Syntax for Stored Procedures" it looks like it allows NULL but balks at NOT NULL and throws an error.

The parameter '@MyArgument' has been declared as NOT NULL. NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions.

There is no value in specifying NULL explicitly - this is the default and only option. There is no declarative syntax for regular stored procs to indicate that parameters must be NOT NULL.

Answer 2

They do different things:

1) @MyArgument INT NULL - NULL values are allowed for parameter @MyArgument

2) @MyArgument INT = NULL - Default value of parameter @MyArgument is NULL