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.
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.