[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
>
>