July 23, 2008 at 9:32 am
is there a way that I can create a view based on a recursive query? code for recursive query:
With downline ( ConsultantID, EffectiveDate, EffectiveEndDate, ConsultantName, SponsorID, SponsorName, SponsorXID, DownLineLevel, ConsultantXId, DeactivationDate, Bumpupdate, NACDate, CurrentLevelXID, CurrentLevelAchieveDate, StatusID, Active, ConsultantDate )
AS ( Select ConsultantID,
EffectiveDate,
EffectiveEndDate,
firstname + ' ' + LastName as ConsultantName,
cast([1stSponsorID] as nvarchar(max)) AS SponsorID,
'' AS SponsorName,
SponsorXID,
0 as DownLineLevel,
xid,
InactiveDate as DeactivationDate,
Bumpup as Bumpupdate,
NACdate,
CurrentLevelXID,
CurrentLevelAchieveDate,
GenealogyStatusID as StatusID,
Active,
CNSDate AS ConsultantDate
FROM [consultant].[uvw_ConsultantDownline] a wITH ( NOLOCK )
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate
and A.EffectiveEndDate
--
UNION ALL
SELECT A.ConsultantID,
A.EffectiveDate,
A.EffectiveEndDate,
A.FirstName + ' ' + A.LastName as ConsultantName,
cast(A.[1stSponsorID] as nvarchar(max)) as SponsorID,
'' As SponsorName,
A.SponsorXID,
DownLineLevel + 1,
A.XID as ConsultantXID
--,A.Active
,
A.InactiveDate AS DeactivationDate,
A.Bumpup as BumpupDate,
A.NACDate
--,A.CurrentLevelAchieveDate
,
A.CurrentLevelXID,
A.CurrentLevelAchieveDate,
A.GenealogyStatusID AS StatusID,
a.Active,
A.CNSDate as ConsultantDate
FROM [consultant].[uvw_ConsultantDownline] AS A with ( nolock )
INNER JOIN DownLine AS B ON A.SponsorXID = B.ConsultantXID
WHERE @MonthEndDt Between a.EffectiveDate
and A.EffectiveEndDate
AND A.Active = 1
AND DownLineLevel + 1 <= 4
)
--Select * from Downline
This query isuse repetatively in different proc and I am wonder if it would be faster in a view?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 23, 2008 at 9:35 am
Syntax below works for commonTableExpressions
create vYourViewHere
as
with cteName as
(...)
select * from cteName
Note: cte's are usable in sprocs the point to remember is to add the semicolon before the WITH
create proc usp_yada
as
set no count on
;with cteName as
( ... )
select * from cteName
return
July 23, 2008 at 9:40 am
Seems straight forward but how would I handle the parameters that the Recursive query uses?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 23, 2008 at 9:49 am
If the parameters are not used for the recursion (and I don't think they are) you can just create the view w/0 the where statement. Then when you access the view add your own where for the consulatant and data.
If that doesn't work I would create a sproc to call within the original sproc
within other sproc
...
create table # (or @) downline
INSERT INTO #downline
Exec usp_getDownline @aConsultantID, @aMonthEndDt
...
July 23, 2008 at 12:03 pm
You can also put the code in a tablevalued function, which accepts parameters.
N 56°04'39.16"
E 12°55'05.25"
July 23, 2008 at 2:01 pm
alorenzini (7/23/2008)
This query isuse repetatively in different proc and I am wonder if it would be faster in a view?
Moving a table expression to a normal view should have no effect on performance (over having the expression in-line). That's actually good, as almost all other ways of abstracting a table expression add overhead.
However, Moving a table expression to an indexed view my make the queries that use it run faster. Like tables & indexes though, it will also take up space, and may add overhead to you DML statements.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply