Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

A Recursive User-Defined Function (SQL Server 2000)

Anyone know what this will return?

create function RecursiveTest(@V int)
returns @t table (i int)
as
   begin
   set @v = @v - 1
   if @v<>0
     insert into @t 
     select @v union
     select * from dbo.RecursiveTest(@v)
   else
     insert into @t values (0)
   return
   end

select * from dbo.RecursiveTest(10)

Try it out!

Unfortunately, the argument must be less than or equal to 32 due to the number of levels of recursion SQL can handle.  It's probably not very efficient, either.  But, it's pretty cool!

Print | posted on Friday, November 21, 2003 4:33 PM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: User-Defined Function Recursion

That is pretty cool!

I actually was trying something like this for the Fibonacci series post and then noticed your post. A nice tool to have for small recursive functions. Although 32 does not give you much headroom.
11/25/2003 9:02 PM | Jay
Gravatar

# re: User-Defined Function Recursion

Yep, this is quite neat, i had built on this idea for to pull through a small hierachy of data for work purposes. Shot!
10/14/2005 11:01 AM | Annesh Singh
Gravatar

# re: A Recursive User-Defined Function (SQL Server 2000)

thnax.

It's really helpful to me.
3/31/2010 10:44 PM | kautilya
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET