Problems using TOP and User Functions

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 r
eturned. 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 Te
rritories
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 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
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
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 could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:

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