1

2

3

4

5

select cwv.ProjectNodeName,cwv.System_AssignedTo, cwv.System_CreatedDate, cwv.System_Id,cwv.System_Title,  wilt.words

 from WorkItemLongTexts wilt

left join

[Tfs_Warehouse].[dbo].[CurrentWorkItemView] cwv on wilt.ID = cwv.System_Id and cwv.System_WorkItemType = '測試用例'

where wilt.fldid = 10181 and wilt.EndDate = '9999-01-01' and  SUBSTRING(wilt.Words,1,6) = '<steps'

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

CREATE PROCEDURE [dbo].[CalcTestSetpProg]

AS

BEGIN

SET NOCOUNT ON; 

 --Delete illegal record

  delete  [TeamProjectTestStep] where isnull(system_id,0)=0

DECLARE @temp TABLE

(

    sys_id INT,

    word ntext

)

INSERT INTO @temp(sys_id, word ) select [System_Id],words from [dbo].[TeamProjectTestStep];

DECLARE

    @sys_id AS INT,

    @word As nvarchar(max)

WHILE EXISTS(SELECT sys_id FROM @temp)

        BEGIN

               SET ROWCOUNT 1

               SELECT @sys_id= sys_id,@word = word FROM @temp;

               BEGIN Try

                UPDATE [TeamProjectTestStep] SET StepCount = Cast(@word as xml).value('count(/steps/step)','int')  WHERE System_Id=@sys_id;

               END Try

               Begin Catch

               End Catch  

               SET ROWCOUNT 0  

               DELETE FROM @temp WHERE sys_id=@sys_id;

        END 

END