PRB: Cannot Pass Function as Parameter to Table Valued User Defined Function (312323)



The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

This article was previously published under Q312323

SYMPTOMS

When you pass a user defined function (UDF) or a system function as an input parameter to a UDF that returns a table variable, the statement fails with the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('
					
If the UDF returns a scalar variable instead of a table variable then it works fine.

CAUSE

SQL Server dissallows anything but compile time constants from being in the parameter list of table valued functions.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. From SQL Query Analyzer, run this code:
    USE pubs
    GO
    
    --F1 is the UDF that returns the table variable.
    CREATE FUNCTION F1(@HOSTNAME nchar(30))
    RETURNS @nodes TABLE ( authorid varchar(20))
    AS
    BEGIN
    INSERT INTO @nodes
    SELECT au_id FROM dbo.authors
    RETURN
    END
    GO
    
    --F2 is the UDF that returns an integer.
    CREATE FUNCTION F2 (@HOSTNAME nchar(30)) 
    RETURNS int 
    AS
    BEGIN 
    RETURN  (1)
    END
    GO
    					

  2. Run the following queries and note that the third query fails with an error.
    -- This query returns 1.
    SELECT dbo.F2(HOST_NAME()) 
    GO
    					
    Result:
    -----------
    1
    -- This query returns one row of authorid.
    SELECT TOP 1 * FROM dbo.F1('Testing')
    GO
    					

    Result:
    authorid
    --------------------
    409-56-7008
    
    --- This query  fails.
    SELECT * FROM dbo.F1(HOST_NAME())
    GO
    					
    Result:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '('.
    						

Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbprb KB312323