Reputation: 179
I have a junction table which holds dependencies between items. I am using this to programmatically create a gantt chart showing these dependencies. I have a working stored procedure now, however my company's policy is to avoid cursors where possible. I put this to the gurus out there, is it possible to do this without the cursor?
DATA:
declare @BaseTable Table
(
[IssueDependencyId] [bigint] IDENTITY(1,1) NOT NULL,
[IssueId] [bigint] NOT NULL,
[DependsOnIssueId] [bigint] NOT NULL
)
INSERT INTO @BaseTable SELECT 48, 0 UNION ALL SELECT 49, 48 UNION ALL SELECT 50, 48 UNION ALL SELECT 51, 48 UNION ALL SELECT 55, 48 UNION ALL SELECT 56, 48 UNION ALL SELECT 52, 49 UNION ALL SELECT 52, 50 UNION ALL SELECT 52, 51 UNION ALL SELECT 53, 52 UNION ALL SELECT 57, 54 UNION ALL SELECT 54, 55 UNION ALL SELECT 57, 56
SELECT * FROM @BaseTable
STORED PROC code:
DECLARE @IssueId int, @DependsOnIssueId int, @StartPoint int, @EndPoint int
SET @StartPoint = 0
SET @EndPoint = 10
DECLARE @ResultsTable TABLE (
IssueId int not null,
DependsOnIssueId int not null,
Start_Point int,
End_Point int
)
Select IssueId, DependsOnIssueId
INTO #tmp1
FROM IssueDependency
WHERE UpperLevelIssueId = 48
ORDER BY DependsOnIssueId
declare MyCursor Cursor for (Select IssueId, DependsOnIssueId from #tmp1);
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @IssueId, @DependsOnIssueId
WHILE @@FETCH_STATUS = 0
BEGIN
--get parent position to set start
SELECT @StartPoint = ISNULL(End_Point, 0)
FROM @ResultsTable WHERE IssueId = @DependsOnIssueId
SET @EndPoint = @StartPoint + 10
INSERT INTO @ResultsTable VALUES
(@IssueId, @DependsOnIssueId, @StartPoint, @EndPoint)
FETCH NEXT FROM MyCursor
INTO @IssueId, @DependsOnIssueId
END
Close MyCursor
DEALLOCATE MyCursor;
SELECT IssueId,
MAX(start_point) max_start_point,
MAX(end_point) max_end_point
INTO #MaxPoints
from @ResultsTable
GROUP BY IssueId
SELECT r.IssueId,DependsOnIssueId,
max_start_point start_point,
max_end_point end_point
FROM @ResultsTable r
JOIN #MaxPoints m ON m.IssueId = r.IssueId
ORDER BY r.IssueId
RESULTING DATA
IssueId DependsOnIssueId Start_Point End_Point
--------------------------------------------------------------------
48 0 0 10
49 48 10 20
50 48 10 20
51 48 10 20
52 49 20 30
52 50 20 30
52 51 20 30
53 52 30 40
54 55 20 30
55 48 10 20
56 48 10 20
57 54 30 40
57 56 30 40
Your help much appreciated!!
Upvotes: 0
Views: 140
Reputation: 43636
Generally, many of the cursor T-SQL statements can be rewrite using Recursive Common Table Expressions Recursive CTE . You can also search for some articles about how performance is better when you are using this technique.
In you case (this is full working example), the solution looks like this:
SET NOCOUNT ON
GO
DECLARE @DataSource TABLE
(
[IssueDependencyId] BIGINT IDENTITY(1,1) NOT NULL,
[IssueId] BIGINT NOT NULL,
[DependsOnIssueId] BIGINT NOT NULL
)
INSERT INTO @DataSource ( [IssueId], [DependsOnIssueId])
VALUES (48, 0)
,(49, 48)
,(50, 48)
,(51, 48)
,(55, 48)
,(56, 48)
,(52, 49)
,(52, 50)
,(52, 51)
,(53, 52)
,(57, 54)
,(54, 55)
,(57, 56)
;WITH DataSource ([IssueId], [DependsOnIssueId], [Start_Point], [End_Point]) AS
(
SELECT AnchorMebemr.[IssueId]
,AnchorMebemr.[DependsOnIssueId]
,0
,10
FROM @DataSource AS AnchorMebemr
WHERE AnchorMebemr.[IssueId] = 48
UNION ALL
SELECT RecursiveMebemer.[IssueId]
,RecursiveMebemer.[DependsOnIssueId]
,DS.[End_Point]
,DS.[End_Point] + 10
FROM @DataSource AS RecursiveMebemer
INNER JOIN DataSource DS
ON RecursiveMebemer.[DependsOnIssueId] = DS.[IssueId]
)
SELECT DISTINCT DS.[IssueId]
,DS.[DependsOnIssueId]
,DS.[Start_Point]
,DS.[End_Point]
FROM DataSource DS
ORDER BY DS.[IssueId]
,DS.[DependsOnIssueId]
SET NOCOUNT OFF
GO
The screenshot below displays the output after the execution of the T-SQL statements above:
Note: I have noticed that in your last row you might have syntax error (as I have understand the logic):
Anyway, If I have misunderstood something, I am sure you have got the idea.
Upvotes: 2
Reputation: 3752
I have not tested this. I am using a autoincrement column to loop through the temp1 table. Here it goes:
DECLARE @tmp1 table
(
_ID int identity (1,1) , -- will be used for looping
IssueId int not null,
DependsOnIssueId int not null
)
DECLARE @i as int
DECLARE @max as int
INSERT INTO @tmp1 (IssueId, DependsOnIssueId )
Select IssueId, DependsOnIssueId
FROM IssueDependency
WHERE UpperLevelIssueId = 48
ORDER BY DependsOnIssueId
SELECT @i = 1, @max = MAX(_ID) FROM @tmp1
WHILE @i <= @max
BEGIN
SELECT @IssueId = IssueId, @DependsOnIssueId = DependsOnIssueId
FROM @tmp1 WHERE _ID = @i
--get parent position to set start
SELECT @StartPoint = ISNULL(End_Point, 0)
FROM @ResultsTable WHERE IssueId = @DependsOnIssueId
SET @EndPoint = @StartPoint + 10
INSERT INTO @ResultsTable VALUES
(@IssueId, @DependsOnIssueId, @StartPoint, @EndPoint)
SET @i = @i + 1
END
Upvotes: 1