Cross apply from function not working

[tshad]

I have the following function:

***************************************
USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fnTops]
(@Top int, @CategoryID int)
RETURNS TABLE
AS
RETURN
SELECT TOP(@Top) ProductName, UnitPrice
FROM dbo.Products
WHERE CategoryID=@CategoryID
ORDER BY UnitPrice DESC;
*****************************************

If I do:

select * from [dbo].[fnTops](3,5)

It returns 3 rows fine.

If I do:

SELECT CategoryID, CategoryName, ProductName, UnitPrice
FROM dbo.Categories C CROSS APPLY
dbo.fnTops(2, C.CategoryID) P
ORDER BY C.CategoryID, ProductName

I get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'C'.

But if I change C.CategoryID to 3, it works fine.

What is wrong here?

Thanks,

Tom

[Plamen Ratchev]

Most likely the compatibility level of the Northwind database is 80 (SQL Server
2000). Change the compatibility level to 90 (SQL Server 2005) or 100 (SQL Server
2008) and try it again. The CROSS APPLY operator was introduced in SQL Server
2005 and while it works if the database is in compatibility level 80 you cannot
pass references from other source tables.

--
Plamen Ratchev
http://www.SQLStudio.com

[tshad]

That was it.

Thanks,

Tom
"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:rbs886tr0geva4oah...@4ax.com...