November 19, 2008 at 4:14 am
Hi,
I am using CTE in a Procedure. While executing the Procedure the CTE should returns null value.
Here i am getting the null result set. But what Problem here is, after CTE i have checking some condition's. That condition not getting executed whenever
my CTE returns null value.
How can i solve this.
---
November 19, 2008 at 5:14 am
Can you post the procedure?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 19, 2008 at 6:57 am
i am getting the following error while executing the procedure.
The statement terminated. The maximum recursion %d has been exhausted before statement completion.
Please help to solve the problem.
November 19, 2008 at 7:04 am
sqluser (11/19/2008)
i am getting the following error while executing the procedure.The statement terminated. The maximum recursion %d has been exhausted before statement completion.
Please help to solve the problem.
There is insufficient information to solve the problem.
Can you please post the procedure?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 19, 2008 at 7:14 am
DECLARE @HieLevel TABLE
(
HieID[uniqueidentifier],
HieLevel[tinyint],
ParentHieID[uniqueidentifier],
Levels[tinyint]
)
;WITH CTEHierLevel(fldHieID,fldHieLevel,fldParentHieID,Level)
AS
(
SELECT
fldHieID,
fldHieLevel,
fldParentHieID,
0 AS Level
FROM
tblHierachey
WHERE
fldHieID = @HieID
UNION ALL
SELECT
Client.fldHieID,
Client.fldHieLevel,
Client.fldParentHieID,
Level + 1
FROM
tblHierachey AS Client
INNER JOIN
CTEHieLevel AS CL
ON (Client.fldParentHieID = CL.fldHieID)
)
INSERT INTO @HierLevel (HieID,HieLevel,ParentHieID,Levels)
SELECT fldHieID,fldHieLevel,fldParentHieID,Level FROM CTEHierLevel order by level
/* Below i am doing some validation */
IF EXISTS(SELECT 1 FROM tblTable AS T INNER JOIN @HierLevel AS H ON (T.HieID = H.JieID)
BEGIN
END
This Hie will tell how many child Hie are the for the parent.
November 19, 2008 at 7:30 am
What happens when you run this simplified version of your query? If you supply some sample data then forum members will be able to test themselves.
[font="Courier New"]
DECLARE @HieID -- assign a datatype
SET @HieID = --and a value
;WITH CTEHierLevel(fldHieID,fldHieLevel,fldParentHieID,LEVEL)
AS
(
SELECT fldHieID,
fldHieLevel,
fldParentHieID,
0 AS [Level]
FROM tblHierachey
WHERE fldHieID = @HieID
UNION ALL
SELECT Client.fldHieID,
Client.fldHieLevel,
Client.fldParentHieID,
LEVEL + 1
FROM tblHierachey AS Client
INNER JOIN CTEHierLevel AS CL -- CHANGED FROM INNER JOIN CTEHieLevel AS CL
ON Client.fldParentHieID = CL.fldHieID
)
SELECT fldHieID, fldHieLevel, fldParentHieID, [Level]
FROM CTEHierLevel
ORDER BY [level]
[/font]
Don't forget to assign a datatype, and a value, to the variable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply