Archives

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:

2 comments to How to Pass GetDate() as a Stored Procedure Parameter

  • Oliver

    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?

  • Sajid

    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

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>