[dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Hi Arthur

I think I recall that article. But wasn't that about a limited/fixed number
of sublevels? True recursion should just continue one level down until no
more levels are found, no matter how many levels.

/gustav

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 28. oktober 2013 16:34
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Gustav,

Yes and no. Somewhere on TechRepublic there's an article by me that
discusses recursive SQL within a stored procedure. If you can't find it, I
have a copy in Word format that I could send you off-list, but their search
is pretty good. Try "recursion in t-sql". If that doesn't work try searching
for my name and that ought to find it.

Anyway, you're right: The WITH <name> AS (SELECT...) construct is T-SQL not
pure SQL. In essence, what you're doing is creating a virtual temp table and
then joining that to the real table (or view).

And you're also wrong. In pure SQL it's quite common to join a table or view
to itself. One typical use of this technique is to find the second-last item
in a set (which you do by joining a set to itself but the WHERE clause uses
a "<" comparison rather than an "=". Combine that with an ORDER BY ... DESC
and you get the previous row. If that's a little fuzzy, I'll supply an
example, but I encourage you to ponder it first. LOL.

Arthur


On Mon, Oct 28, 2013 at 11:06 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Arthur
>
> So the WITH .. AS isolates the inner part?
> Normally, in pure SQL, you can't let a query pull data from itself 
> without getting a circular reference error or the like.
>
> /gustav