Hi! I'm trying to create an User Defined function that gives me the top rows of a table. I must be able to define wich is the top number of rows to be returned. I assume I could do this:

CREATE FUNCTION ListTopRegions (@reg_id int, @num int)
RETURNS @table table ([TerritoryID] [nvarchar] (20), [TerritoryDescription] [nchar] (50))

AS
begin
if exists (select 1 from Region where RegionID = @reg_id)
begin
insert into @table select top @num TerritoryID, TerritoryDescription from Territories
where RegionID=@reg_id
end
return
end

but it keeps saying : Incorrect syntax near '@num'. Any idea what I'm doing wrong? I'm using the Northwing DB, and I intend to get the first 5 entries, for instance. -- TechnoSpike ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message967438.html

You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005.  You didn't say what
determines the order.  For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:

select
     o.TerritoryID
,    o.TerritoryDescription
from
    Territories    o
join
    Territories    i    on    i.TerritoryID <= o.TerritoryID
                            and  i.RegionID    = o.RegionID
where
     o.RegionID = @reg_id
group by
     o.TerritoryID
,    o.TerritoryDescription
having
    count (*) <= @num

Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:

CREATE FUNCTION ListTopRegions (@reg_id int, @num int)
RETURNS @table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))

AS
begin
if exists (select 1 from Region where RegionID = @reg_id)
begin
insert into @table select top @num TerritoryID, TerritoryDescription
from Territories
where RegionID=@reg_id
end
return
end

but it keeps saying : Incorrect syntax near '@num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.

You could use the SET ROWCOUNT command.  This will limit the number of rows
returned from the query similar to the TOP function.

Show quoteHide quote

Performance of SQL Server Stored Proc slows down 300% over a few weeks
SQL Query Analyzer
Question about the use of filegroups
GETDATE() as default value is out of sync
dbcc checkdb gets hung
Start up SQL Server
Stored prodcedure sytnax
select dynamic field
Copy userdata
Backup takes 100% of the CPU