How do I Pass a value into a with statement from my sql statement

Question

Post reply

How do I Pass a value into a with statement from my sql statement

Aaron Tracy

Old Hand

Points: 308
More actions
December 9, 2008 at 9:09 am

#125559

I need to get the right parent record to start with. And I need to do it for each row in my select statement. How do I pass in the processID to the with statement from the running SQL statement?
WITH FolderStructure (ParentID, FolderID, Name, Level)
AS
(
-- Anchor member definition
SELECT f.ParentID, f.FolderID, f.Name,
0 AS Level
FROM Folder AS f
WHERE FolderID = 391--@TestCaseLibraryFolder-- this needs to be the 2 - Master Test Case Library folder for the project this is related to.
UNION ALL
-- Recursive member definition
SELECT f.ParentID, f.FolderID, f.Name,
Level + 1
FROM Folder AS f
INNER JOIN FolderStructure AS fs
ON f.ParentID = fs.FolderID
),
TestSteps (ProcessID, ExecProcessID, TestStepID, ComponentActionID,
Narrative , CertifyValue, Level, CertifySequence, ComponentActionParmsID,
FullPath)
AS
(
-- Anchor member definition
SELECT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, 1 as Level, ts.CertifySequence, tsa.ComponentActionParmsID,
CAST (ts.CertifySequence AS VARCHAR(MAX))
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
WHERE ts.ProcessID= p.ProcessID
UNION ALL
SELECT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, Level + 1, ts.CertifySequence, tsa.ComponentActionParmsID,
tst.FullPath +'/' + CAST (ts.CertifySequence AS VARCHAR(MAX))
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
Inner Join TestSteps as tst
ON ts.ProcessID = tst.ExecProcessID
)
Select ( Select 'Step ' + LTrim(str(CertifySequence + 1)) + ': ', Narrative + char(13) from testSteps
order by FullPath For XML PATH('')
) TCSteps
FROM FolderStructure as fs
INNER JOIN Process AS p ON fs.FolderID = p.ProcessFolderID
order by Level

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

1. Remove the WHERE from your CTE.

2. Add the line

INNER JOIN TestStep T ON P.ProcessID= T.ProcessID

to your final select

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Aaron Tracy Old Hand Points: 308 More actions · Answer 2

FROM FolderStructure as fs

INNER JOIN Process AS p ON fs.FolderID = p.ProcessFolderID

INNER JOIN TestStep T ON P.ProcessID= T.ProcessID

order by Level

I added this line and it breaks the recursive side of the function. The child records go away and I just get the first record repeated 6 times. I should only get 3 records returned... any other ideas?

Thank you for helping me out!

Aaron Tracy Old Hand Points: 308 More actions · Answer 3

Hmm... No replies... maybe I'm not explaining this simply enough. Think of this problem this way... Let's say I have a folder table that looks like this:

FolderID ParentID FolderName

1 Null Root

2 1 My Folder1

3 1 My Folder 2

4 3 My SubFolder 1

5 4 My Sub Sub Folder

Now I want to use a CTE to get the folder structure, however, I want to start not at the root, but at the My Folder 2 and get the sub folders for each. If I put in the first part of my CTE a where statement of 'where FolderID = 3' I don't have a problem. However, I need to be able to dynamically select the 'root' folder in my select statement. Does anyone have an example of how to do this?

Thanks so much!

Aaron