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

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

-----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 14:40
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Gustav,

Following the WITH is the name of the alias being defined, along with its
parameter, and its definition follows the first AS. Now jump down to the
INNER JOIN line, and notice that it's calling itself (i.e. the alias just
defined).

So each row has a menuid and also a parentid. What the JOIN does is link the
child rows to their parent using their parentid to their parent's menuid. In
other words it's a self-join; the table is joined to itself. The child rows'
parentid contains the menuid that identifies its parent row.
Because it's an INNER JOIN, eventually it will fail, in which case you've
traversed the tree all the way to its outermost leaves.

Does that help?

Arthur


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

> Hi all
>
> I'm a noob when it comes to T-SQL - try to limit myself to tables and
views
> - so could someone please explain what is going on here?
> What does this WITH mean?
> How would you retrieve the data from Access?
> Is "vParent = null" valid code in T-SQL? Why not "vParent is null"?
>
> <SQL>
> WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL) AS
> (
>     SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu
WHERE vParent = null
>     UNION ALL
>     SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM
dbo.SecurityMenu
>     INNER JOIN security_menu_Recursive AS smr ON smr.menuid =
dbo.SecurityMenu.vParent
> )
> SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive 
> </SQL>
>
> The full tip is here:
>
>
>
http://www.codeproject.com/Articles/674287/Recursive-Queries-in-Microsoft-SQ
L-Server-2008
>
> /gustav