Is it possible to create a Function that takes a table as a parameter and returns a table?

Question

I need to create a function that takes a table (or table variable) as an input parameter and returns a table-value as a parameter. Is this possible with the following constraints:

• SQL Server 2005
• CLR function is not an option (should be TSQL-only)

Any example code as a starter would be helpful.

Answer 1

You can not use table parameter types until SQL Server 2008.

One option is to use xml to pass in the table and XPath to parse it.

Answer 2

I tried, but no, @gbn is correct, you can't in SQL Server 2005

here is my try:

CREATE FUNCTION dbo.TestTable
(
    @InputTable  table (RowID int, DataValue varchar(10))
)
RETURNS
table (RowID int, DataValue varchar(10))
AS
    SELECT * FROM @InputTable ORDER BY 1 DESC
RETURN 
go

DECLARE @t table (RowID int, DataValue varchar(10))

INSERT INTO @t VALUES (3,'cccc')
INSERT INTO @t VALUES (2,'bbbb')
INSERT INTO @t VALUES (1,'aaaa')

select * from @t
select * from dbo.TestTable(@t)

here are the errors:

Msg 156, Level 15, State 1, Procedure TestTable, Line 3
Incorrect syntax near the keyword 'table'.
Msg 1087, Level 15, State 2, Procedure TestTable, Line 8
Must declare the table variable "@InputTable".
Msg 1087, Level 15, State 2, Line 1