SP Date parameter default to GETDATE()?

Question

Post reply

SP Date parameter default to GETDATE()?

dan-572483

SSCoach

Points: 15870
More actions
July 17, 2013 at 5:34 pm

#280189

I want my procedure to include an optional date parameter that defaults to the current date. Why does the following code show a Syntax error?
CREATE PROCEDURE sp_MyProcedure
@Record_ID Varchar(12),
@OtherKey_ID Int,
@DateParameter DATE = GETDATE(),
@Comments VARCHAR(MAX) = NULL
AS ....
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

I don't believe you can use GETDATE() in that way as a default parameter value.

Try setting a default of NULL and replacing that NULL in your code with GETDATE()

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

dan-572483 (7/17/2013)
I want my procedure to include an optional date parameter that defaults to the current date. Why does the following code show a Syntax error?
CREATE PROCEDURE sp_MyProcedure
@Record_ID Varchar(12),
@OtherKey_ID Int,
@DateParameter DATE = GETDATE(),
@Comments VARCHAR(MAX) = NULL
AS ....

I'm not sure either why you can't use GETDATE() as a default.

But since it is a default, you don't need to even have it as an input parameter.

Declare it inside the procedure itself then you should have no problems. If I use GETDATE()

in any procedure I always set it to a local variable as a constant first then whenever it's needed it will

be the same DATETIME throughout the procedure. Otherwise, if you call GETDATE() within the

procedure you will get slightly different time values each time. Sometimes you want that, but

usually I want it as a constant. So I do this:

CREATE PROCEDURE foo

AS

BEGIN

SET NOCOUNT ON

DECLARE

@CurrDate DATETIME

,@OtherVariables,etc

SET @CurrDate = GETDATE()

....rest of the procedure....

....etc...

END

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

If you take a look in Books Online, the reason becomes crystal clear. The hilighting is mine...

default
Is a default value for the parameter. If a default value is defined, the procedure can be executed without specifying a value for that parameter. [font="Arial Black"]The default must be a constant or it can be NULL[/font].

Why they made it that way, one can only guess but GETDATE() is patently not a constant for these purpose nor is even a perpetually fixed result function such as PI().

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

CREATE PROCEDURE prBlah

@DateParameter DATE = NULL

AS

SET @DateParameter = ISNULL(@DateParameter, GETDATE())

...

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Instead of specifying it in parameter list you can assign GETDATE() to local variable inside stored procedure body:

CREATE PROCEDURE sp_MyProcedure

@Record_ID Varchar(12),

@OtherKey_ID Int,

@Comments VARCHAR(MAX) = NULL

AS

BEGIN

DECLARE @DateParameter DATE

SET @dateparameter = GETDATE()

Also refer Jeff comments for more explanation..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

kapil_kk (7/18/2013)
Instead of specifying it in parameter list you can assign GETDATE() to local variable inside stored procedure body:
CREATE PROCEDURE sp_MyProcedure
@Record_ID Varchar(12),
@OtherKey_ID Int,
@Comments VARCHAR(MAX) = NULL
AS
BEGIN
DECLARE @DateParameter DATE
SET @dateparameter = GETDATE()
..................
END
Also refer Jeff comments for more explanation..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

OTF SSCertifiable Points: 6054 More actions · Answer 7

kapil_kk (7/18/2013)
Instead of specifying it in parameter list you can assign GETDATE() to local variable inside stored procedure body:
CREATE PROCEDURE sp_MyProcedure
@Record_ID Varchar(12),
@OtherKey_ID Int,
@Comments VARCHAR(MAX) = NULL
AS
BEGIN
DECLARE @DateParameter DATE
SET @dateparameter = GETDATE()
Also refer Jeff comments for more explanation..

I think the OP would like the option of being able to supply a date value to the Procedure which I don't believe he will be able to do with the approach you've specified.

He will have to create an input parameter and default it to NULL, setting it to whatever Date he desires in the body of the procedure when one is not supplied.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

OTF (7/18/2013)
kapil_kk (7/18/2013)
Instead of specifying it in parameter list you can assign GETDATE() to local variable inside stored procedure body:
CREATE PROCEDURE sp_MyProcedure
@Record_ID Varchar(12),
@OtherKey_ID Int,
@Comments VARCHAR(MAX) = NULL
AS
BEGIN
DECLARE @DateParameter DATE
SET @dateparameter = GETDATE()
Also refer Jeff comments for more explanation..
I think the OP would like the option of being able to supply a date value to the Procedure which I don't believe he will be able to do with the approach you've specified.
He will have to create an input parameter and default it to NULL, setting it to whatever Date he desires in the body of the procedure when one is not supplied.

Yes you are right if user want that parameter value in output then he needs to pass a default value Null then he can change the value in the body of the stored procedure..... For this scenario my approch will fail

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

dan-572483 SSCoach Points: 15870 More actions · Answer 9

Correct. Users need to be able to optionally set a date other than the current date. Sean's suggestion to default to null and then use ISNULL() set it to current if null looks like the way to go.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

This code shows how to setup an optional DATE parameter that will use the input passed or default to the current date if it isn't.

go

create procedure sp_MyProcedure

@DateParameterdate = null

as

set @DateParameter = isnull(@DateParameter,getdate())

select [@DateParameter] = @DateParameter

go

exec sp_MyProcedure

go

exec sp_MyProcedure @DateParameter = '20121231'

go

if object_id('sp_MyProcedure','P') is not null drop procedure sp_MyProcedure

Results:

@DateParameter

--------------

2013-07-18

(1 row(s) affected)

@DateParameter

--------------

2012-12-31

(1 row(s) affected)