Problem with TOP and User Functions

[TechnoSpike]

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'm using the Northwing DB, and I intend to get the first 5 entries, for instance.

 

[tc3596]

Try this...

Exec ('Select top ' + @num + 'cus_no, cus_name from arcusfil_sql')

...insert into @table
Exec ('Select top ' + @num + ' TerritoryID, TerritoryDescription from Territories Where RegionID= ' + @reg_id)

 

[tc3596]

sorry, dropped some code in by mistake. Just use this...

...insert into @table
Exec ('Select top ' + @num + ' TerritoryID, TerritoryDescription from Territories Where RegionID= ' + @reg_id)

or this may work as well...

Exec ('insert into ' + @table + ' Select top ' + @num + ' TerritoryID, TerritoryDescription from Territories Where RegionID= ' + @reg_id)

 

[TechnoSpike]

Hum....sorry, doesn't work either....when I use this:

CREATE FUNCTION ListTopRegionsN (@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 Exec (' Select top ' + @num + ' TerritoryID, TerritoryDescription from Territories Where RegionID= ' + @reg_id)
end
return
end

I get this error:
Error 197: EXECUTE cannot be used as a source when inserting into a table variable.

And when I do this:

CREATE FUNCTION ListTopRegionsN (@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
Exec ('insert into'+ @table+' Select top ' + @num + ' TerritoryID, TerritoryDescription from Territories Where RegionID= ' + @reg_id)
end
return
end

I get the following error:

Error 137: Must declare the variable @table.

Any idea on a possible solution?

 

[tc3596]

hmmm... it works outside of a function. I know that...I'll try working on it some more.

 

[tc3596]

It appears that you cannot use Exec with table variables inside udf's.


Here is an alternative...


1st create a stored procedure..

CREATE PROCEDURE usp_GetTerritories

@reg_id int,
@num int

AS

Exec('Select
Top ' + @num +
' TerritoryID, TerritoryDescription from Territories
where RegionID' + @reg_id)

-------------

2nd, call this code whereever you were before

CREATE TABLE #Territories(TerritoryID nvarchar(20),
TerritoryDescription nchar(50))

INSERT INTO #Territories
--usp_GetTerritories @reg_id, @num
EXEC usp_GetTerritories 1, 8

Select * From #Territories

Drop Table #Territories

 

[tc3596]

forgot the = sign after "where RegionId"..
should be "where RegionId = ' + @reg_id)"..