Help with Recursive Function

I am writing a function which I hope to use as a column value in a
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.

When I test the funciton, it says 'commands completed successfully',
but nothing is returned. This is in SQL2000, runing on Windows2000.
The table the function acts on is:

CREATE TABLE [dbo].[tblbenthictaxa] (
[tsn] [int] IDENTITY (1, 1) NOT NULL ,
[rank_id] [int] NOT NULL ,
[dir_parent_tsn] [int] NULL ,
[req_parent_tsn] [int] NOT NULL ,
[taxa_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

ReqParentTSN is the recursive link to rows in the table;
Level is the taxonomic level the user requested (an integer
representing Order, Family, Genus or Species).

CREATE FUNCTION dbo.CBN_RecursTaxa (
@.ReqParentTSN int,
@.Level int
)
RETURNS varchar(100) AS

BEGIN

Declare @.Rank int,
@.taxaname varchar(100)

SELECT @.ReqParentTSN = tblbenthictaxa.req_parent_tsn,
@.TaxaName = tblbenthictaxa.taxa_name,
@.Rank = tblbenthictaxa.rank_id
FROM tblbenthictaxa
WHERE tblbenthictaxa.TSN=@.ReqParentTSN

if @.Rank > @.Level
**1 --exec CBN_RecursTaxa @.ReqParentTSN, @.Level

RETURN @.TaxaName
END

Thanks in advance for any help,

TimTim Pascoe (tim.pascoe@.cciw.ca) writes:
> I am writing a function which I hope to use as a column value in a
> select query. The function recursively walks a taxonomic heirarchy,
> extracting the name for an organism at the taxonomic level requested
> by the user. I'm having trouble figuring out the syntax to call the
> function from itself (see **1), and the value returned.
> When I test the funciton, it says 'commands completed successfully',
> but nothing is returned. This is in SQL2000, runing on Windows2000.
> The table the function acts on is:

There are two ways to run a scalar UDF, and I don't know which you are
using. But I think this example, gives you the answer to both of your
questions:

CREATE FUNCTION nisse (@.i int) returns varchar(200) AS
BEGIN
DECLARE @.ret varchar(200),
@.tmp varchar(200)
SELECT @.i = @.i - 1, @.ret = 'nisse '
IF @.i > 0
BEGIN
EXEC @.tmp = dbo.nisse @.i
SELECT @.ret = @.tmp + @.ret
END
RETURN @.ret
END
go
SELECT dbo.nisse(4)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

The example was perfect. I knew it was something small, but the simple
things are sometimes the hardest to track down when you are learning.

The function works perfectly, and is much faster than the original ASP
script approach I had.

Thanks again,

Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql