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.
Very Interesting. I have been struggling with this very issue.
I would be interested to see how you would make it production ready. Are there advantages to making it a SQL Fuction?
When I execute I face Error I.E(Conversion failed when converting date and/or time from character string.)
Please Reply…
DECLARE @PID int
DECLARE @SDATE DATETIME = NULL
DECLARE @ParaName varchar(50)
SET @ParaName=’MyName’
SET @SDATE= GETDATE()
–COALESCE(‘@SDATE’,GETDATE())
SELECT @PID = ProfileId FROM tGeneralProfileCX where Name=@ParaName AND CreatedOn=CONVERT(Varchar,’@SDATE’,100)
print pid