How to Pass GetDate() as a Stored Procedure Parameter

The Problem

When you pass GetDate() as a parameter to a stored procedure, the following error is returned:

EXEC dbo.ProcDate GetDate()

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’

When I ran into this problem, I found this helpful article which suggests that I should assign the value of GetDate() to variable and then pass the variable as the parameter. While that does work, I wasn’t in a position where I could use a variable so I needed to find another solution.

The Solution

My solution was fairly simple. Define a default value for the parameter as GetDate() like so.

IF EXISTS
      (SELECT name
         FROM sysobjects
        WHERE name = N'ProcDate' AND type = 'P')
 DROP PROCEDURE ProcDate
GO
 
CREATE PROCEDURE ProcDate
   @Date DATETIME = NULL
AS
   SET @Date = coalesce (@Date, Getdate ())
 
   SELECT @Date
GO

Now the stored procedure works correctly. If a date is passed as a parameter, the stored procedure uses it, otherwise it defaults to GetDate().

Exec.ProcDate '11/23/2011' -- returns 2011-11-23 00:00:00.000
 
Exec.ProcDate              -- returns 2011-11-12 15:57:27.623

Incidentally, I realize that this stored procedure isn’t “production ready” because it will fail if someone passes “Bleh” in as the parameter. I deliberately left out the error checking to give avoid clutter.

Related posts: