getdate() error

[katbear]

Help.

Why won't this work?

alter PROCEDURE myProc
(
  @AsOfDate DATETIME = GETDATE(),
)

Error: Incorrect syntax near '('.

Thanks

 

[SQLDenis]

I beleive the reason is because it is non deterministic
even if it is not true you can not use a function as a value here

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

alter PROCEDURE myProc
(
@AsOfDate DATETIME = NULL,
)
AS
if @AsOfDate is null set @AsOfDate = GETDATE()

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLDenis]

or set @AsOfDate = coalesce(@AsOfDate,getdate())

alter PROCEDURE myProc
(
  @AsOfDate DATETIME = NULL,
)
AS

set @AsOfDate = coalesce(@AsOfDate,getdate())

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

This is pure speculation but I have the feeling that the IF statement is more efficient than the Coalesce method.

My reasoning is that checking a single condition, in terms of the CPU execution steps necessary to carry it out, is more efficient than always making an assignment. In the case that the variable is not null, one less step has to be taken.


IS NULL, IF:
check for nullness, set variable to single expression.

IS NOT NULL, IF:
check for nullness.

IS NULL, Coalesce:
check for nullness, set variable to single expression.

IS NOT NULL, Coalesce:
check for nullness, set variable to single expression.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[katbear]

Thanks all.

 

[katbear]

What does "non-deterministic" mean?

 

[ESquared]

It means that given the same inputs, a function is not guaranteed to have the same output each time it is invoked.

A deterministic function always gives the same output for the same input. Power(10, 2) will always yield 100, every time. Getdate(), even with no inputs, yields a different value each time.

But I was under the impression that expressions couldn't be SP default values no matter what kind of expression they were, deterministic or not. Only literals are allowed (unless I'm wrong and if so please someone tell me).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[katbear]

thanks for the clarification