2

I want to set GetDate() as default parameter value. But it shows error. Below is my block of code:

Create Proc XYS
(
   @myDate datetime = GetDate()  //error

)
AS
SET @myDate = GetDate() // i can do this but neither i want this nor I want to 
                           pass current date from front end or upper layer


....
....

As far as i know functions/dynamic values are not supported as this level, instead we are allowed hardcoded values. Any workaround?

  • Possible duplicate: http://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure – Michael Baker Aug 13 '13 at 07:53
  • Check this out - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5b9a7d78-6c09-437c-ad01-87981a3f478f/getdate-in-stored-procedure – Vivek Jain Aug 13 '13 at 07:55

2 Answers2

2

Would suggest using a value (e.g. 0 = minimum datetime) and then checking for it in your stored proc:

Create Proc XYS
(
   @myDate datetime = 0)
AS
BEGIN
    IF @myDate = 0
        SET @myDate = GetDate()

    // ...
END
Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
0

I'm not saying it's the best idea in the world but it works for me:

CREATE VIEW dbo.V_GetDate
AS
SELECT     GETDATE() AS TheDate

then 

Create Proc XYS
AS
declare @myDate datetime
Select @myDate = TheDate from dbo.V_GetDate
cleantone
  • 83
  • 1
  • 9