Problem using TOP in User Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem using TOP in User Functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-24 : 08:04:05

John writes "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 can do to bypass this? I've seen your FAQ, regarding TOP in Procedures, but, that solution doesn't suit my needs (it's for a group project and I would prefer having the SQL structured the same away other users are using it).I'm using the Northwing DB, and I intend to get the first 5 entries, for instance."

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-24 : 10:19:31

Assuming that you want the top based on the ordering of the TerritoryID key, you could try:

SELECT
t.TerritoryID,
t.TerritoryDescription
FROM Territories T
JOIN Territories T1 ON T1.ID <= T.ID
WHERE T.RegionID = @Reg_ID
AND T1.RegionID = @Reg_ID
HAVING COUNT(*) <= @Num

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-24 : 11:51:54

TOP n does not allow a variable.

You could declare a table in your function with an integer identity column, ID, insert the data into that table in the order you want it returned, and then select the data where ID <= @num and orderd by ID.

You could also use a stored procedure, instead of a function, and use dynamic SQL inside the stored proc to set the TOP n.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources