Recursion and UDFs in sql 2000

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)

Recursion and UDFs in sql 2000

Author

Topic

tribune
Posting Yak Master

105 Posts

Posted - 2001-12-28 : 00:36:56

I was playing around with UDFs and wrote a little recrusive function to sum all the digits in a number (IE: "123" = 6), and noticed something about the return syntax of a function. This script will generate the following error:

IF OBJECT_ID('SumDigits') IS NOT NULL
DROP FUNCTION SumDigits
GO

/* Sums up all of the individual digits in a number */
CREATE FUNCTION dbo.SumDigits
(
@Num bigint
)
RETURNS tinyint
AS

BEGIN

IF @Num > 10
RETURN dbo.SumDigits(@Num / 10) + @Num % 10
ELSE
RETURN @Num

END

Server: Msg 455, Level 16, State 2, Procedure SumDigits, Line 65535
The last statement included within a function must be a return statement.

However, the following will work fine:

/* Sums up all of the individual digits in a number */

IF OBJECT_ID('SumDigits') IS NOT NULL
DROP FUNCTION SumDigits
GO

CREATE FUNCTION dbo.SumDigits
(
@Num bigint
)
RETURNS tinyint
AS

BEGIN
DECLARE @tmp tinyint

IF @Num > 10
SET @tmp = dbo.SumDigits(@Num / 10) + @Num % 10
ELSE
SET @tmp = @Num

RETURN @tmp
END

GO

SET NOCOUNT ON
SELECT dbo.SumDigits(123)

----
6

Is it just me or does anyone else seem to find the return syntax for UDF's using recursive a bit annoying? I mean not only are there more lines of code to write to add to confusion, but you've also got another variable declaration and two extra assignments. Plus you are calling the function over and over until the condition is met (Is there a level limit on recursive function calls like there is for sprocs?) so this problem is magnified even more...

And whats up with my line error being Line 65535? LOL! :p

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-28 : 14:16:40

Maybe you find it annoying, but I find it to be a nice enforcement of good practice. I believe it's bad practice to dump out of the middle of a function or subroutine. I always code my functions (regardless of programming language) to assign a variable the return value and return it at the end. That way I always have only one entry point and one exit point of any function.

I don't have the BOL for SQL 2000 handy to tell you the limit on recursion in UDF's, but I'd bet large sums of money there is one. You could probably find it in your version of BOL. Why not just write this function without recursion? One way would be to convert the int to a string, parse the string and sum each character. Perhaps not as neat and fancy, but it'll avoid the possible recursion nightmare looming on the horizon.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

monkeybite
Posting Yak Master

152 Posts

Posted - 2001-12-28 : 15:26:14

quote:

Why not just write this function without recursion? One way would be to convert the int to a string, parse the string and sum each character. Perhaps not as neat and fancy, but it'll avoid the possible recursion nightmare looming on the horizon.

One could also insert into a table variable, and SELECT SUM(). There's almost always a way around recursion, and rarely is it more efficient to use recursion over another method.

-- monkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources