locked
GETDATE() as Table-Valued Function Parameter RRS feed

  • Question

  • I can do:

    SELECT TOP (100) [t0].[ClaimID], [t0].[ReserveCategory], [t0].[Amount], [t0].[SifFundYear]
    FROM [sifTran].[tvfSifTranGrossReserves]('2012-12-31 00:00:00.000') AS [t0]

    But I can't do:

    SELECT TOP (100) [t0].[ClaimID], [t0].[ReserveCategory], [t0].[Amount], [t0].[SifFundYear]
    FROM [sifTran].[tvfSifTranGrossReserves](GETDATE()) AS [t0]

    I get:

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near '('.

    I guess one can not call functions as parameters to table-valued functions?


    Thanks/Regards, Mike DePouw

    Thursday, October 4, 2012 5:42 PM

Answers

  • @Kalman Toth

    Thanks for the reply.  I tried your example and it of course worked.

    I was eventually able to track the problem down: the database was set to SQL 2000 (80) for a compatibility level.  I changed it to 2005 (90) and I was able to execute the statement above without receiving a syntax error.


    Thanks/Regards, Mike DePouw

    • Marked as answer by Mike DePouw Thursday, October 11, 2012 12:56 PM
    Thursday, October 11, 2012 12:56 PM

All replies

  • In the first example, the parameter is a string. In the second, it's a date.
    Thursday, October 4, 2012 5:49 PM
  • As an alternative you might want to use a variable to assign the GETDATE() value and pass on to the table valued functions

    DECLARE @date_variable AS DATETIME
    SET @date_variable = GETDATE()
    
    SELECT TOP (100) [t0].[ClaimID], [t0].[ReserveCategory], [t0].[Amount], [t0].[SifFundYear]
    FROM [sifTran].[tvfSifTranGrossReserves](@date_variable) AS 
    [t0]


    Hima, Please don't forget to mark it as answer or vote as helpful if it helps.

    Thursday, October 4, 2012 5:56 PM
  • Try this:

    SELECT TOP 100 t0.ClaimID, t0.ReserveCategory, t0.Amount, t0.SifFundYear
    FROM sifTran.tvfSifTranGrossReserves(CONVERT(nvarchar,GETDATE()))

    I haven't tested it as I don't have a table like that, but it's worth a shot.


    Kyle Masters
    Business and Financial Intelligence Analyst - Firewind Digital Studios
    Contributor - The SQL School

    Thursday, October 4, 2012 5:58 PM
  • Can you post the function code?

    See working demo:

    CREATE FUNCTION tvfTestDatetime (@Input datetime)
    RETURNS @OUT TABLE (ID INT, DT datetime)
    AS
    BEGIN
    	INSERT @OUT SELECT TOP (10) ProductID, @Input
    	FROM Production.Product;
    RETURN ;
    END
    GO
    
    SELECT * FROM tvfTestDatetime ('2012-01-01 01:01:01');
    GO
    -- (10 row(s) affected)
    SELECT * FROM tvfTestDatetime (getdate());
    GO
    -- (10 row(s) affected)
    
    SELECT * FROM tvfTestDatetime (CURRENT_TIMESTAMP);
    GO
    -- (10 row(s) affected)


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Thursday, October 4, 2012 6:01 PM
    • Proposed as answer by Samuel Lester - MSFTMicrosoft employee Thursday, October 4, 2012 6:11 PM
    • Marked as answer by Naomi N Wednesday, October 10, 2012 3:02 PM
    • Marked as answer by Carl Liu Tuesday, October 16, 2012 5:03 AM
    • Unmarked as answer by Carl Liu Tuesday, October 16, 2012 5:03 AM
    • Marked as answer by Pan Zhang Tuesday, October 16, 2012 6:18 AM
    • Unmarked as answer by Pan Zhang Tuesday, October 16, 2012 6:26 AM
    Thursday, October 4, 2012 6:00 PM
  • Correct. This is one many reason that good SQL do not use proprietary "features" like Table-valued functions. The RDBMS model is based on scalar values (1NF) for data. T-SQL is a simple one-pass compiler; it was not meant to use thunks . Yes, that is the actual term in compiler writing for passing expressions as parameters; read the history of Algol-60. 

    What we do in a declarative language like SQL is use VIEWs, CTEs and derived tables. The optimizer can use them directly without being compelled to materialize them. Your mindset is still locked in FORTRAN, BASIC and COBOL with a magnetic tape file system. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 4, 2012 9:28 PM
  • @Kalman Toth

    Thanks for the reply.  I tried your example and it of course worked.

    I was eventually able to track the problem down: the database was set to SQL 2000 (80) for a compatibility level.  I changed it to 2005 (90) and I was able to execute the statement above without receiving a syntax error.


    Thanks/Regards, Mike DePouw

    • Marked as answer by Mike DePouw Thursday, October 11, 2012 12:56 PM
    Thursday, October 11, 2012 12:56 PM