KiwiSunGoddess
KiwiSunGoddess

Reputation: 179

Can I do without this cursor

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

Answers (2)

gotqn
gotqn

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:

enter image description here

Note: I have noticed that in your last row you might have syntax error (as I have understand the logic):

enter image description here

Anyway, If I have misunderstood something, I am sure you have got the idea.

Upvotes: 2

unlimit
unlimit

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

Related Questions