Problems using TOP and User Functions
-
Hi to all!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.
-
SQL 2K and older does not support SELECT TOP @Number type syntax. This is a new feature with SQL 2K5.
If you need to return a dynamic TOP n unfortunately, you will need to use dynamic SQL for now...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You may be able to get away with inserting a reasonable number of "TOP" rows (say 100) into a temp table with an ID column using a Cursor. Then select from the temp table where ID <= @num.
That's a little ugly but it might work for you.
-
Just found your answer:
declare @v1 int
set @v1 = 25
set rowcount @v1
select * from MyTable Order by DateColumn
set rowcount 0
For a full explanation check this article: http://www.sqlteam.com/item.asp?ItemID=233
-
Just out of curiosity. How will you get dynamic sql inside a UDF to work?
And a note on SET ROWCOUNT. This limits the rows processed thereafter. You might want to carefully test that you get the expected result.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That's the problem...I manage to use the code shown here in a procedure, but I can't use it in a User Defined Function! I want to return the result of the select as a table, and I wanted to do that using the UDF.... but having to use the TOP, I can't work it out, sooo...anymore ideas?
-
Could try a cursor but I don't recall if there are any limitations on UDFs with cursors.
Look here for a post I did with cursor for same sort of thing
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=167772#bm168457
-
Does it need to be a UDF at all?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It could be a Stored Procedure, but I can't figure it out how I can return a table in a stored procedure. Can anybody give me a hint?
-
Well you can return a table in the sp just by doing a select. but the problem is that you can use a stored proc in an inner join. is that what's causing you a headache?
-
Remi,
Can a stored procedure return a table to a calling sp?
thanks, ron
-
Well yes, but that takes a little work.. As I said a sp cannot be used in a straight select (you can't do Select * from exec dbo.MyProc). You can however call a spb from a spa where spb selects some data and that data will be returned to the client just the same as if spa had done the select.
Or if you want to execute a sp and use the data returned in the stored proc, you can do something like this :
Create table #Objects
(
id int not null,
name varchar(128) not null
)
GO
Create procedure dbo.MySPa
AS
Select top 100 percent id, name from dbo.SysObjects order by name, id
GO
Insert into #Objects (id, name) exec dbo.MySPa
Select * from #Objects
drop table #Objects
drop Procedure MySPa
-
Ok, thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply