SQL User-Defined Functions: Fetching TOP n records in a user-defined function...

Question

How come the following doesn't work?

CREATE FUNCTION Test (@top integer)

RETURNS TABLE

AS

RETURN

SELECT TOP @top * FROM SomeTable

GO

I just want to be able to be able to specify the number of results to be returned. [SQL Server 2000.]

Thanks!

Answer 1

Support for this was added to SQL Server 2005, but it's not available in 2000. You'd have to use (shudder) dynamic sql or some other number trick instead.

Answer 2

CREATE FUNCTION Test (@top integer)

RETURNS TABLE

AS

RETURN

SELECT TOP (@top) * FROM SomeTable

GO

However without an ORDER BY clause it is not very meaningful (the order of the results is not guaranteed).

Answer 3

Oops a variable TOP is not available in SQL Server 2000. Nor is Dynamic SQL supported in any version.

Answer 4

For MS SQL 2000, you can use:

CREATE FUNCTION Test (@top integer)

RETURNS TABLE

AS

SET ROWCOUNT @top

RETURN SELECT * FROM SomeTable

Answer 5

Jake, try setting the rowcount to your function parameter and then doing your select. I have not tried this, YMMV.

From: http://msdn.microsoft.com/en-us/library/aa259189(SQL.80).aspx

Syntax

SET ROWCOUNT { number | @number_var }
Arguments

number | @number_var

Is the number (an integer) of rows to be processed before stopping the given query.