Why can't GETDATE() be used as the default value of a procedure parameter or a value in an EXECUTE statement?

Question

Consider the following stored procedure:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME = GETDATE()
AS
BEGIN
   -- Do something spectacular
END

Upon invocation, the parameter declaration fails with the error, "Error converting data type nvarchar to date." This can be worked around by changing the code as follows:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME = NULL
AS
BEGIN

    IF @myDateTimeParam IS NULL
        SET @myDateTimeParam = GETDATE();

   -- Do something spectacular
END

However, assume that @myDateTimeParam did not have a default value:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME
AS
BEGIN
   -- Do something spectacular
END

In this case, you cannot simply pass GETDATE() as a formal parameter as follows:

EXEC MySchema.MyProcedure GETDATE()

as this also produces the dreaded "Error converting data type nvarchar to date" error. The only workaround to this is to first declare a variable and then pass the variable:

DECLARE @myDateTimeParam DATETIME = GETDATE();
EXEC MySchema.MyProcedure @myDateTimeParam;

Why is this? Both the source and target data types are DATETIME. In theory, a data type conversion error should not occur when using the result of GETDATE() as either the default value of a parameter or the value of a formal parameter.

Is there some technical reason that this does not work? There's nothing in the MSDN documentation that indicates that it should not work.

Answer 1

This is covered in the documentation, CREATE PROCEDURE (Transact-SQL), under the default subheading in the arguments section:

A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

Emphasis mine.

GETDATE() is not a constant, so cannot be used an a DEFAULT value. Hence why you need to use the format below, as then the value of GETDATE() is determined at run time:

CREATE PROC YourProc @Param date = NULL
AS

    IF @Param IS NULL BEGIN
        SET @Param = GETDATE();
    END;
    ...