收录日期:2019/03/26 02:59:00 时间:2009-07-29 18:20:26 标签:sql-server-2005,function

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.

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.

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