8

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.

Mike Hofer
  • 16,477
  • 11
  • 74
  • 110
  • 1
    What SQL Server version are you targeting ? `CREATE OR ALTER` was added only recently. In any case, default values have to be constants. That's documented clearly in [CREATE PROCEDURE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017) – Panagiotis Kanavos Dec 18 '18 at 12:27
  • "because you can't" is unfortunately the most satisfying reason as to why you can't use a function call where a parameter is expected, see https://stackoverflow.com/questions/2399104/incorrect-syntax-near-calling-storedproc-with-getdate – Alex K. Dec 18 '18 at 12:27
  • to me it sounds like "because it isn't very good" is an answer – Cato Dec 18 '18 at 13:32
  • 1
    @Cato expressing a dislike of SQL Server isnt helpful to the OP. – Thom A Dec 18 '18 at 13:45
  • @Larnu - I didn't say I didn't like SQL server, I'm saying that that part of it looks poorly designed and just a little shoddy. He's asking 'why'. It looks to me that the writers of the parser didn't bother implementing the ability to give a function as a parameter, whereas languages like VB.net have thought it through, you could send the result of a function call to even a reference parameter. – Cato Dec 18 '18 at 14:50
  • ..additionally it's important to manage a person's expectations of the product. We're not a 'SQL fan club', we've got to mention any 'below par' features. I don't work for Microsoft, I'm not selling it to anyone. – Cato Dec 18 '18 at 15:06
  • Saying a product isn't very good is expressing dislike. If you had said "MySQL isn't very good", or "Python isn't very good" I would have made the same comment. – Thom A Dec 18 '18 at 15:08

1 Answers1

9

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;
    ...
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you. That clears up one part of the question. Now I just have to figure out what's going on with `EXECUTE`. :) – Mike Hofer Dec 18 '18 at 12:28
  • 3
    Just found this in the `EXECUTE` documentation: "If you pass a single word that does not begin with @ and that's not enclosed in quotation marks - for example, **if you forget @ on a parameter name - the word is treated as an nvarchar string, in spite of the missing quotation marks**." I guess that explains it. – Mike Hofer Dec 18 '18 at 12:32
  • Correct, @MikeHofer. So, if you were to do something like `EXEC YourProc GETDATE;` it would be interpreted as `EXEC YourProc N'GETDATE'`;. Adding the brackets then turns it into something like `EXEC YourProc N'GETDATE'();`, which makes no sense to the compiler. – Thom A Dec 18 '18 at 12:38
  • Huh @Cato? What are is that even in reference to? – Thom A Dec 18 '18 at 13:44
  • if I send 'GETDate()' to his stored proc as a parameter, I get the different error message. It doesn't seem to be treating the text as a varchar – Cato Dec 18 '18 at 14:51
  • Considering that the parameter in the OP's SP is a `datetime` , @Cato, you will get an error. The **literal string** `'GETdate()'` can't be converted to a `datetime`. – Thom A Dec 18 '18 at 15:06
  • that's GETdate() in my quotes as in quoting what was sent, not in the actual text of the code. – Cato Dec 18 '18 at 15:08