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 BEGIN
101 DELETE FROM #T1
102 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 #T1
106
107 UPDATE dbo.PUB_FEATURE_DTS_LINK_TEMP
108 SET feature_id = b.feature_id
109 FROM dbo.PUB_FEATURE_DTS_LINK_TEMP a
110 LEFT JOIN (SELECT pbi,feature_name,feature_id FROM dbo.PUB_FEATURE_MEASUREMENT_INFO WHERE pbi=@pbi) b
111 ON a.pbi=b.pbi AND a.feature_name=b.feature_name
112 WHERE b.feature_id is not null AND a.pbi=@pbi AND a.version_id=@versionId
113
114 UPDATE dbo.PUB_FEATURE_DTS_LINK_TEMP
115 SET dts_feature_id = b.dts_feature_id
116 FROM dbo.PUB_FEATURE_DTS_LINK_TEMP a
117 LEFT JOIN (SELECT pbi,dts_feature_name,dts_feature_id FROM dbo.PUB_FEATURE_DTS_INFO WHERE pbi=@pbi AND feature_id='00000000') b
118 ON a.pbi=b.pbi AND a.dts_feature_name=b.dts_feature_name
119 WHERE b.dts_feature_id is not null AND a.pbi=@pbi AND a.version_id=@versionId
120
121 UPDATE PUB_FEATURE_DTS_INFO
122 SET feature_id = b.feature_id
123 FROM PUB_FEATURE_DTS_INFO a
124 LEFT JOIN (SELECT pbi,feature_id,dts_feature_id FROM PUB_FEATURE_DTS_LINK_TEMP WHERE pbi=@pbi AND version_id=@versionId) b
125 ON a.pbi=b.pbi AND a.dts_feature_id=b.dts_feature_id
126 WHERE a.feature_id='00000000' AND b.feature_id is not null AND a.pbi=@pbi
127
128
129 END
130 --ELSE--which means this feature name has more than one row.
131 --BEGIN
132
133 --END
134 */
135 FETCH NEXT FROM feature_cursor
136 INTO @feature_name,@dts_feature_name
137 END
138
139 CLOSE feature_cursor
140 DEALLOCATE feature_cursor
141 SELECT feature_name, dts_feature_name FROM dbo.PUB_FEATURE_DTS_LINK_TEMP WHERE pbi = @pbi AND version_id = @versionId
142
143 UPDATE a
144 SET feature_id = b.feature_id
145 FROM PUB_FEATURE_DTS_LINK_TEMP a
146 JOIN PUB_FEATURE_MEASUREMENT_INFO AS b ON a.pbi = b.pbi
147 AND a.feature_name = b.feature_name
148 AND a.version_id = @versionId
149 AND a.pbi = @pbi
150
151 PRINT '1. changed rows:' + CAST(@@rowcount AS VARCHAR(50))
152
153 UPDATE a
154 SET dts_feature_id = b.dts_feature_id
155 FROM PUB_FEATURE_DTS_LINK_TEMP a
156 JOIN PUB_FEATURE_DTS_INFO AS b ON a.pbi = b.pbi
157 AND b.feature_id = '00000000'
158 AND a.dts_feature_name = b.dts_feature_name
159 WHERE b.dts_feature_id IS NOT NULL
160 AND a.pbi = @pbi
161 AND a.version_id = @versionId
162
163 PRINT '2. changed rows:' + CAST(@@rowcount AS VARCHAR(50))
164
165 UPDATE a
166 SET feature_id = b.feature_id
167 FROM PUB_FEATURE_DTS_INFO a
168 JOIN PUB_FEATURE_DTS_LINK_TEMP b ON a.pbi = b.pbi
169 AND b.version_id = @versionId
170 AND b.pbi = @pbi
171 AND a.feature_id = '00000000'
172 AND a.dts_feature_id = b.dts_feature_id
173 WHERE b.feature_id IS NOT NULL
174
175 PRINT '3. changed rows:' + CAST(@@rowcount AS VARCHAR(50))
176
177
178
179
180 --DELETE FROM PUB_FEATURE_LINK_IMPORT
181 --WHERE version_id = @versionId
182 -- AND pbi = @pbi
183
184
185
186
187 END
