Invalid syntax near ')' calling stored procedure using GETDATE

Question

Maybe I have a “day” moment, but can someone explain why I get

Message 102, Level 15, State 1, Line 2
Invalid syntax next to ')'.

When running

CREATE PROC DisplayDate (@DateVar DATETIME) AS BEGIN SELECT @DateVar END GO EXEC DisplayDate GETDATE(); 

Answer 1

You cannot pass a function call as an argument to your stored procedure. Use an intermediate variable instead:

 DECLARE @tmp DATETIME SET @tmp = GETDATE() EXEC DisplayDate @tmp; 

Answer 2

As Mitch Wheat it is mentioned that you cannot pass a function.

If in your case you must pass a pre-calculated value or GETDATE () - you can use the default value. For example, modify the stored procedure:

 ALTER PROC DisplayDate ( @DateVar DATETIME = NULL ) AS BEGIN set @DateVar=ISNULL(@DateVar,GETDATE()) --the SP stuff here SELECT @DateVar END GO 

And then try:

 EXEC DisplayDate '2013-02-01 00:00:00.000' EXEC DisplayDate 

Note : here I assumed that NULL is not used for this parameter. If this is not your case, you can use another unused value, for example, '1900-01-01 00: 00: 00.000'