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

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!

From stackoverflow

• 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.

  Jake : ...that is ridiculous. ;)

• 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).

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

• For MS SQL 2000, you can use:

  CREATE FUNCTION Test (@top integer)
  
  RETURNS TABLE
  
  AS
  
  SET ROWCOUNT @top
  
  RETURN SELECT * FROM SomeTable
  

• 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.

  Terrapin : You can't used set rowcount in a user-defined function