Hi Fabio,
It looks like T-SQL parser bug. You can work around it by using local variable.
DECLARE @dt datetime
SET @dt = GETDATE()
SELECT * FROM dbo.fn_Table1(@dt)
SELECT * FROM dbo.fn_Table2(@dt)
There's also way to call getdate() function inside UDF but first you must turn on the data access server option so you can refer to your local server as a linked server.
EXEC sp_serveroption <server_name>, 'data access', 'true'
CREATE FUNCTION dbo.ufn_Today()
RETURNS datetime
AS
BEGIN
DECLARE @dt datetime
SELECT @dt = today
FROM OPENQUERY(< server_name >, 'SELECT getdate() AS today')
RETURN @dt
END
GO
SELECT dbo.ufn_Today()
>Hi,
>
>I have found MSQL documentation good ( not optimal !).
>
>Then, on UDF i can't use GETDATE() ( a big limit ),
>but:
>
>
>CREATE FUNCTION dbo.fn_Scalar(@today DATETIME)
> RETURNS BIT
>AS
> BEGIN
> RETURN 1
> END
>
>CREATE FUNCTION dbo.fn_Table1(@today DATETIME)
>RETURNS TABLE
>RETURN SELECT CONVERT(BIT,1) value
>
>CREATE FUNCTION dbo.fn_Table2(@today DATETIME)
>RETURNS @R_TABLE TABLE (value BIT)
>BEGIN
> INSERT @R_TABLE SELECT 1
> RETURN
>END
>
>
>
>SELECT dbo.fn_Scalar(GETDATE())
>
>SELECT * FROM dbo.fn_Table1(GETDATE())
>
>SELECT * FROM dbo.fn_Table2(GETDATE())
>
>
>
>Syntax error is a TSQL parser bug ?
>
>If No:
>- why TSQL have this limit ? I have big need of it.
>- it is documented ?
>
>Fabio
--sb--