Default Datetime value in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Default Datetime value in stored procedure

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-28 : 12:43:14

Hi,

Any idea why default value assignment of getdate() throws syntax error? I guess its something to do with getdate() or datetime variable. if its a varchar I'm able to assign the value.

CREATE PROCEDURE test1
@start_date DATETIME = getdate(),
@end_date DATETIME = getdate()
AS
BEGIN
SELECT * FROM TBLA
END
GO

If the above one is not possible I can go with the below approach..
CREATE PROCEDURE test1
@start_date DATETIME, -- = getdate(),
@end_date DATETIME, -- = getdate()
AS
BEGIN
SELECT @start_date = ISNULL(@start_date,getdate()),@end_date = ISNULL(@end_date,getdate())
SELECT * FROM TBLA
END
GO

Thanks
Karunakaran

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-28 : 13:06:55

getdate is non deterministic and it looks like it can't be the default value.
set the parameters to getdate() at the begining of your sproc.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-28 : 13:36:34

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-30 : 09:08:05

Is that your full procedure code?
I dont see you using those variables in the select statement

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-02 : 19:18:20

quote:
Originally posted by madhivanan

Is that your full procedure code?
I dont see you using those variables in the select statement

Madhivanan

Failing to plan is Planning to fail

Its not the full procedure code, it just a sample of what I was trying to do.

Thanks
Karunakaran

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 19:23:14

Default them to null and check for null later in code. if they are null, replace with getdate()

Peter Larsson
Helsingborg, Sweden

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-02 : 20:45:00

quote:
Originally posted by Peso

Default them to null and check for null later in code. if they are null, replace with getdate()

Peter Larsson
Helsingborg, Sweden

I just did not default it to NULL.I assumed if there is no input parameter value it will always be null.

quote:


If the above one is not possible I can go with the below approach..
CREATE PROCEDURE test1
@start_date DATETIME, -- = getdate(),
@end_date DATETIME, -- = getdate()
AS
BEGIN
SELECT @start_date = ISNULL(@start_date,getdate()),@end_date = ISNULL(@end_date,getdate())
SELECT * FROM TBLA
END
GO

Thanks
Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources