April 9, 2006 at 2:12 am
DECLARE
@userid uniqueidentifier
SET @userid = 'EC3C39B4-0E80-4EE9-A235-364A17B3A92C'
----------------------
--My First Recursive
----------------------
WITH
Arbo (UserId,UserName)as (
SELECT UserId,UserName FROM aspnet_Users WHERE UserId = @userid
UNION ALL
SELECT a.UserId,a.UserName FROM aspnet_Users a JOIN Arbo ar ON ar.UserId = a.UserIdManager
)
SELECT UserId,UserName FROM Arbo
----------------------
--My Second Recursive
----------------------
WITH
Arbo2 (UserIdManager,UserName,UserIdManager)as(
SELECT UserId,UserName,UserIdManager FROM aspnet_Users WHERE UserId = @userid AND UserIdManager IS NOT NULL
UNION ALL
SELECT a.UserId,a.UserName,a.UserIdManager FROM aspnet_Users a JOIN Arbo2 ar ON ar.UserIdManager = a.UserId
)
SELECT UserIdManager,UserName FROM Arbo2
-- But I would like to do UNION ALL between my two recursive
SELECT
UserIdManager,UserName FROM Arbo2
UNION
ALL SELECT UserId,UserName FROM Arbo
Is
it possible ?
I have this
message : Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword
'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 336
, Level 15, State 1, Line 20
Incorrect syntax near
'Arbo2'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.
degrem_m
Degremont
April 9, 2006 at 7:54 pm
1. The message about the semicolon is part of the answer:
<...>
SET @userid = 'EC3C39B4-0E80-4EE9-A235-364A17B3A92C';
WITH Arbo (UserId,UserName)as (
<...> )
SELECT UserId,UserName FROM Arbo;
----------------------
--My Second Recursive
----------------------
WITH Arbo2 (UserIdManager,UserName,UserIdManager)as(
<...>
2. To perform the UNION, store the results of the first CTE in a table variable, and use it with the second CTE:
DECLARE @Arbo table(UserID int, UserName varchar(30));
WITH Arbo (UserId,UserName)as (
SELECT UserId,UserName FROM aspnet_Users WHERE UserId = @userid
UNION ALL
SELECT a.UserId,a.UserName FROM aspnet_Users a JOIN Arbo ar ON ar.UserId = a.UserIdManager
)
INSERT @Arbo(UserID, UserName)
SELECT UserId,UserName FROM Arbo;
--My Second Recursive
WITH Arbo2 (UserIdManager,UserName,UserIdManager)as(
SELECT UserId,UserName,UserIdManager FROM aspnet_Users WHERE UserId = @userid AND UserIdManager IS NOT NULL
UNION ALL
SELECT a.UserId,a.UserName,a.UserIdManager FROM aspnet_Users a JOIN Arbo2 ar ON ar.UserIdManager = a.UserId
)
SELECT UserIdManager,UserName FROM Arbo2
UNION
SELECT UserID, UserName FROM @Arbo;
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply