Why is DEFAULT Keyword Allowed in CREATE PROCEDURE Command

Question

In the following statement, the DEFAULT keyword appears where a default value is defined for a parameter.

CREATE PROCEDURE usp_Test
    @sp_param1 varchar(20) = DEFAULT
AS
    SELECT @sp_param1 AS myTest
;

NULL is assigned to @sp_param1. But, The default value must be a constant or the NULL keyword.

Why is the DEFAULT keyword allowed in this situation?

Answer 1

The DEFAULT keyword is a placeholder for something that you want to provide, but is not really even NULL. Most commonly seen in

exec SProcName 2, DEFAULT

Even though the 2nd param is supplied, it is not actually a value, but is an instruction to use the DEFAULT value of the 2nd param.

When used in the CREATE PROC itself, it parses, but is simply treated as NULL.