Help with Recursive Function

[Tim Pascoe]

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_Gene ral_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_RecursT axa (
@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=@ReqParentT SN

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

RETURN @TaxaName
END


Thanks in advance for any help,

Tim

[Erland Sommarskog]

Re: Help with Recursive Function

Tim Pascoe (tim.pascoe@cci w.ca) writes:[color=blue]
> 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:[/color]

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/techinfo/productdoc/2000/books.asp

[tim pascoe]

Re: Help with Recursive Function


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!