Microsoft KB Archive/312323

= PRB: Cannot Pass Function as Parameter to Table Valued User Defined Function =

Article ID: 312323

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.



Steps to Reproduce Behavior
  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

  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 '('.



Keywords: kbprb KB312323

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.