1 USE [Dashboard_REL]  2 GO  3 /****** Object:  StoredProcedure [dbo].[PUB_IMPORT_FEATURE_LINK_TSHARP_PROC]    Script Date: 09/09/2014 10:17:30 ******/  4 SET ANSI_NULLS ON  5 GO  6 SET QUOTED_IDENTIFIER ON  7 GO  8   9 ALTER PROCEDURE [dbo].[PUB_IMPORT_FEATURE_LINK_TSHARP_PROC] 10     -- Add the parameters for the stored procedure here 11     @pbi VARCHAR(32) , 12     @versionId VARCHAR(64) 13 AS  14     BEGIN 15     -- SET NOCOUNT ON added to prevent extra result sets from 16     -- interfering with SELECT statements. 17         SET NOCOUNT ON ; 18  19     -- Insert statements for procedure here 20         DECLARE @feature_name VARCHAR(128) 21         DECLARE @dts_feature_name VARCHAR(128) 22         DECLARE @feature_name_number INT  23  24         DECLARE feature_cursor CURSOR scroll 25         FOR SELECT feature_name, dts_feature_name FROM dbo.PUB_FEATURE_LINK_IMPORT WHERE pbi = @pbi AND version_id = @versionId     26          27         SELECT feature_name, dts_feature_name FROM dbo.PUB_FEATURE_LINK_IMPORT WHERE pbi = @pbi AND version_id = @versionId     28  29         OPEN feature_cursor 30  31         FETCH FIRST FROM feature_cursor 32     INTO @feature_name,@dts_feature_name 33  34         WHILE ( @@fetch_status = 0 )  35             BEGIN 36              --added by yulei on 2014-03-11,first check if this T# feature has duplicated names. 37                 SELECT  @feature_name_number = COUNT(1) 38                 FROM    dbo.PUB_FEATURE_MEASUREMENT_INFO 39                 WHERE   feature_name = @feature_name 40                         AND pbi = @pbi  41                 PRINT ISNULL(@feature_name_number,999999)                42                 IF ( @feature_name_number = 1 )--which means it has no duplicated names. 43                     BEGIN                         44                         INSERT  INTO PUB_FEATURE_DTS_LINK_TEMP 45                                 ( feature_name , 46                                   dts_feature_name , 47                                   pbi , 48                                   version_id 49                                 ) 50                                 SELECT  a.* 51                                 FROM    ( SELECT    @feature_name feature_name , 52                                                     Value dts_feature_name , 53                                                     @pbi pbi , 54                                                     @versionId version_id 55                                           FROM      dbo.SplitStr(@dts_feature_name, 56                                                               ‘,‘, 1) 57                                         ) AS a 58                                         LEFT JOIN PUB_FEATURE_DTS_LINK_TEMP AS b ON a.pbi = b.pbi 59                                                               AND a.dts_feature_name = b.dts_feature_name 60                                                               AND a.feature_name = b.feature_name 61                                                               AND b.version_id=@versionId 62                                 WHERE   b.id IS  NULL  63  64                         PRINT ‘inserted rows:‘ 65                             + CAST(@@rowcount AS VARCHAR(50)) 66  67               68                     END 69                   ELSE 70                       BEGIN                         71                         INSERT  INTO PUB_FEATURE_DTS_LINK_TEMP 72                                 ( feature_name , 73                                   dts_feature_name , 74                                   pbi , 75                                   version_id 76                                 ) 77                                 SELECT  a.* 78                                 FROM    ( SELECT    @feature_name feature_name , 79                                                     Value dts_feature_name , 80                                                     @pbi pbi , 81                                                     @versionId version_id 82                                           FROM      dbo.SplitStr(@dts_feature_name, 83                                                               ‘,‘, 1) 84                                         ) AS a 85                                          86  87                         PRINT ‘inserted rows new:‘ 88                             + CAST(@@rowcount AS VARCHAR(50)) 89  90               91                     END 92                      93                      94                      95  96             /* 97              --added by yulei on 2014-03-11,first check if this T# feature has duplicated names. 98              SELECT @feature_name_number=COUNT(*) FROM dbo.PUB_FEATURE_MEASUREMENT_INFO WHERE feature_name=@feature_name AND pbi=@pbi 99              IF(@feature_name_number=1)--which means it has no duplicated names.100              BEGIN101                     DELETE FROM #T1102                     INSERT INTO #T1 EXEC PUB_SPLIT_PROC @dts_feature_name,‘,‘103 104                     INSERT INTO dbo.PUB_FEATURE_DTS_LINK_TEMP (feature_name,dts_feature_name,pbi,version_id)105                     SELECT @feature_name,dts_name,@pbi,@versionId FROM #T1106 107                     UPDATE dbo.PUB_FEATURE_DTS_LINK_TEMP108                        SET feature_id = b.feature_id109                       FROM dbo.PUB_FEATURE_DTS_LINK_TEMP a110                     LEFT JOIN (SELECT pbi,feature_name,feature_id FROM dbo.PUB_FEATURE_MEASUREMENT_INFO WHERE pbi=@pbi) b111                     ON a.pbi=b.pbi AND a.feature_name=b.feature_name112                     WHERE b.feature_id is not null AND a.pbi=@pbi AND a.version_id=@versionId113 114                     UPDATE dbo.PUB_FEATURE_DTS_LINK_TEMP115                        SET dts_feature_id = b.dts_feature_id116                       FROM dbo.PUB_FEATURE_DTS_LINK_TEMP a117                     LEFT JOIN (SELECT pbi,dts_feature_name,dts_feature_id FROM dbo.PUB_FEATURE_DTS_INFO WHERE pbi=@pbi AND feature_id=‘00000000‘) b118                     ON a.pbi=b.pbi AND a.dts_feature_name=b.dts_feature_name119                     WHERE b.dts_feature_id is not null AND a.pbi=@pbi AND a.version_id=@versionId120 121                     UPDATE PUB_FEATURE_DTS_INFO122                        SET feature_id = b.feature_id123                       FROM PUB_FEATURE_DTS_INFO a124                     LEFT JOIN (SELECT pbi,feature_id,dts_feature_id FROM PUB_FEATURE_DTS_LINK_TEMP WHERE pbi=@pbi AND version_id=@versionId) b125                     ON a.pbi=b.pbi AND a.dts_feature_id=b.dts_feature_id126                     WHERE a.feature_id=‘00000000‘ AND b.feature_id is not null AND a.pbi=@pbi127 128              129              END130              --ELSE--which means this feature name has more than one row.131              --BEGIN132              133              --END134             */135                 FETCH NEXT FROM feature_cursor136         INTO @feature_name,@dts_feature_name137             END138 139         CLOSE feature_cursor140         DEALLOCATE feature_cursor141               SELECT feature_name, dts_feature_name FROM dbo.PUB_FEATURE_DTS_LINK_TEMP WHERE pbi = @pbi AND version_id = @versionId142 143         UPDATE  a144         SET     feature_id = b.feature_id145         FROM    PUB_FEATURE_DTS_LINK_TEMP a146                 JOIN PUB_FEATURE_MEASUREMENT_INFO AS b ON a.pbi = b.pbi147                                                           AND a.feature_name = b.feature_name148                                                           AND a.version_id = @versionId149                                                           AND a.pbi = @pbi150 151         PRINT ‘1. changed rows:‘ + CAST(@@rowcount AS VARCHAR(50))152                                                           153         UPDATE  a154         SET     dts_feature_id = b.dts_feature_id155         FROM    PUB_FEATURE_DTS_LINK_TEMP a156                 JOIN PUB_FEATURE_DTS_INFO AS b ON a.pbi = b.pbi157                                                   AND b.feature_id = ‘00000000‘158                                                   AND a.dts_feature_name = b.dts_feature_name159         WHERE   b.dts_feature_id IS NOT NULL160                 AND a.pbi = @pbi161                 AND a.version_id = @versionId162 163         PRINT ‘2. changed rows:‘ + CAST(@@rowcount AS VARCHAR(50))164   165         UPDATE  a166         SET     feature_id = b.feature_id167         FROM    PUB_FEATURE_DTS_INFO a168                 JOIN PUB_FEATURE_DTS_LINK_TEMP b ON a.pbi = b.pbi169                                                     AND b.version_id = @versionId170                                                     AND b.pbi = @pbi171                                                     AND a.feature_id = ‘00000000‘172                                                     AND a.dts_feature_id = b.dts_feature_id173         WHERE   b.feature_id IS NOT NULL174                175         PRINT ‘3. changed rows:‘ + CAST(@@rowcount AS VARCHAR(50))176                   177        178 179        180         --DELETE  FROM PUB_FEATURE_LINK_IMPORT181         --WHERE   version_id = @versionId182         --        AND pbi = @pbi183 184 185 186 187     END