sql server programming: Strange thing in MSSQL 2000 which doesn't occur in MSSQL 2005 -- sql server programming

Re: Strange thing in MSSQL 2000 which doesn't occur in MSSQL 2005

SQL Menace
4/26/2007 4:08:07 PM

sql server programming: On Apr 26, 6:30 pm, "Alain Quesnel" <alainsanss...@logiquel.com>
[quoted text, click to view]

because getdate is deterministic in sql server 2005 but not in sql
server 2000

read this: http://sqlservercode.blogspot.com/2006/02/user-definded-functions-and-getdate.html

Denis The SQL Menace
http://sqlservercode.blogspot.com/

Strange thing in MSSQL 2000 which doesn't occur in MSSQL 2005

Alain Quesnel
4/26/2007 6:30:05 PM

Try to compile this function:

USE NORTHWIND
GO
create function dbo.fnTest (@inCustomerID nchar(5),
@inDate datetime)
returns table
as
return
(
select * from ORDERS
where OrderDate = @inDate and
CustomerID = @inCustomerID
)

Can someone tell me why this works in MSSQL 2000:
declare @date datetime
set @date = GetDate()
select * from dbo.fnTest('ALFKI', @date)

and not this:
select * from dbo.fnTest('ALFKI', GetDate())
?

Both calls to the function work in MSSQL 2005, BTW.

Thank you,

Alain Quesnel
alainsansspam@logiquel.com

www.logiquel.com