· 8 years ago · Feb 09, 2018, 05:16 PM
1USE AccountDB
2GO
3ALTER PROC licensing.BatchUserCreate
4 @OrganizationId UNIQUEIDENTIFIER ,
5 @MemberID UNIQUEIDENTIFIER ,
6 @TotProcessed INT OUTPUT
7AS /*
8
9 ///////////////////////////////////////////////////////////////////////////////////////////////////
10 Purpose
11 -------
12
13 Use Cases
14 ---------
15 exec AccountDB.BatchUserCreate '949b46eb-1168-4f41-8449-b69eeac1e42f','85258989-91c8-428a-9ff7-76db2cad5ab4',0
16 exec AccountDB.BatchUserCreate '21b19a96-8d07-41dc-b598-39730cf3ec5c','f4d1425a-5193-4878-8121-27f7baf7c370',0
17 exec AccountDB.BatchUserCreate '21B19A96-8D07-41DC-B598-39730CF3EC5C', 'F4D1425A-5193-4878-8121-27F7BAF7C370',0
18 Modification History
19 --------------------
20 15-02-2017 - Prashanta Ganguly
21 - Batch upload for user assignment for a particular account
22
23 20-02-2017 - Prashanta Ganguly
24 - Add logging entry for errors
25
26 22-02-2017 - Prashanta Ganguly
27 - Alter the entire procedure to make it more optimized for bulk process
28 ///////////////////////////////////////////////////////////////////////////////////////////////////
29 */
30 BEGIN
31 BEGIN TRANSACTION;
32 BEGIN TRY
33 SET NOCOUNT OFF
34 DECLARE @TotRecord INT ,
35 @Number INT,
36 @Counter INT ,
37 @PhoenixID INT ,
38 @PersonId UNIQUEIDENTIFIER ,
39 @IsProcessed BIT ,
40 @IsAssignable BIT ,
41 @BUFirstName varchar(100) ,
42 @BULastName varchar(100) ,
43 @BUEmail varchar(150) ,
44 @BUPhoenixID INT ,
45 @Description varchar(100),
46 @needtocheckinEnterprise BIT,
47 @CreateWebsiteUserAndPhoenixCustomerInBulk BIT
48
49 SELECT @TotRecord = 0 ,
50 @Counter = 1 ,
51 @Number =0,
52 @PhoenixID = NULL ,
53 @IsProcessed = 0 ,
54 @TotProcessed = 0 ,
55 @IsAssignable = 1 ,
56 @BUFirstName = NULL , --Batch upload FirstName provided in excel
57 @BULastName = NULL , --Batch upload LastName provided in excel
58 @BUEmail = NULL , --Batch upload LastName provided in excel
59 @BUPhoenixID = NULL, --Batch upload LastName provided in excel
60 @PersonId = NULL
61
62 DECLARE @FirstName varchar(100) ,
63 @Lastname varchar(100) ,
64 @Email varchar(150) ,
65 @Password varchar(90) ,
66 @License varchar(80) ,
67 @Add_ons varchar(2000) ,
68 @Newsletters varchar(2000)
69
70
71 SELECT @FirstName = '' ,
72 @Lastname = '' ,
73 @Email = '' ,
74 @Password = '' ,
75 @License = '' ,
76 @Add_ons = '' ,
77 @Newsletters = '',
78 @Description = ''
79 SELECT Number ,
80 PhoenixID ,
81 FirstName ,
82 LastName ,
83 Email ,
84 [License] ,
85 [Add_ons] ,
86 [Newsletters] ,
87 [Password],
88 [User],
89 IsUserProcessed,
90 IsAssignable,
91 IsUserAssigned,
92 [Description]
93 INTO #TmpBatchUploadUser
94 FROM AccountDB.Licensing.[BatchUploadUser] (NOLOCK)
95 WHERE [User] = @MemberID ORDER BY Number
96
97 SELECT @TotRecord = COUNT(1)
98 FROM #TmpBatchUploadUser
99 WHERE [User] = @MemberID;
100
101 CREATE TABLE #TmpCreateUser
102 (
103 Customer UNIQUEIDENTIFIER,
104 [Address] UNIQUEIDENTIFIER,
105 FirstName varchar(100),
106 LastName varchar(100),
107 Email varchar(150),
108 [Password] varchar(90)
109 )
110
111 CREATE TABLE #TmpUserEnterprise
112 (
113 UserCounter INT,
114 Number int,
115 PhoenixID INT,
116 FirstName varchar(100),
117 LastName varchar(100),
118 Email varchar(150),
119 [Password] varchar(90)
120 )
121
122 CREATE TABLE #TmpDuplicate
123 (
124 Number int,
125 PhoenixID INT,
126 FirstName varchar(100),
127 LastName varchar(100),
128 Email varchar(150),
129 )
130
131 DECLARE @TmpPhoenixId INT;
132 DECLARE @UserCounter int
133 SELECT @UserCounter = 0
134 WHILE ( @Counter <= @TotRecord )
135 BEGIN
136 SELECT @IsProcessed = 0 ,
137 @IsAssignable = 0 ,
138 @BUFirstName = '',
139 @BULastName ='',
140 @BUEmail ='' ,
141 @BUPhoenixID = 0,
142 @Description ='',
143 @needtocheckinEnterprise = 0,
144 @CreateWebsiteUserAndPhoenixCustomerInBulk = 0,
145 @PersonId = NULL
146
147 SELECT @Number = Number ,
148 @PhoenixID = PhoenixID ,
149 @FirstName = FirstName ,
150 @Lastname = LastName ,
151 @Email = Email ,
152 @Password = [Password] ,
153 @License = License ,
154 @Add_ons = Add_ons ,
155 @Newsletters = Newsletters
156 FROM #TmpBatchUploadUser
157 WHERE Number = @Counter;
158
159 --IF EXISTS(SELECT * FROM #TmpDuplicate WHERE (Email = @Email AND PhoenixID = 0) OR (Email = '' AND PhoenixID = @PhoenixID) OR (Email = @Email AND PhoenixID = @PhoenixID) OR (PhoenixID = @PhoenixID AND @PhoenixID > 0) OR (Email = @Email AND @PhoenixID =''))
160 IF EXISTS (SELECT * FROM #TmpDuplicate WHERE ((Email = @Email AND (@PhoenixID = 0 OR @PhoenixID IS null)) OR
161 ((@Email = '' OR @Email IS NULL) AND PhoenixID = @PhoenixID) OR
162 (Email = @Email AND PhoenixID = @PhoenixID AND @PhoenixID > 0)))
163 BEGIN
164 PRINT 'Duplicate'
165 SELECT @Description ='Duplicate Record'
166 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
167 END
168 ELSE
169 INSERT INTO #TmpDuplicate VALUES (@Number,@PhoenixID,@FirstName,@Lastname,@Email)
170 IF ((@PhoenixID IS NULL OR @PhoenixID < 1) AND (@Email IS NULL OR @Email =''))
171 BEGIN
172 SELECT @Description ='Customer ID or Email is missing'
173 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
174 END
175 ELSE
176 IF ((@Email IS NULL OR AccountDB.dbo.IsValidEmail(RTRIM(LTRIM(@Email))) = 0) AND ( @PhoenixID IS NULL OR @PhoenixID < 1 ))
177 BEGIN
178 SELECT @Description ='Invalid Email ID or Customer ID'
179 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
180 END
181 ELSE
182 IF (AccountDB.dbo.IsValidEmail(RTRIM(LTRIM(@Email))) = 1 AND (@PhoenixID IS NULL OR @PhoenixID < 1) AND ((@FirstName IS NULL OR @FirstName ='') OR (@Lastname IS NULL OR @Lastname = '')))
183 BEGIN
184 SELECT @Description ='FirstName or LastName is Invalid'
185 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
186 END
187 ELSE
188 IF (AccountDB.dbo.IsValidEmail(RTRIM(LTRIM(@Email))) = 1 AND NOT exists(SELECT CustomerID FROM Phoenix.dbo.Customer(NOLOCK) WHERE CustomerID = @PhoenixID AND @PhoenixID IS NOT NULL AND @PhoenixID <> '') AND ((@FirstName IS NULL OR @FirstName ='') OR (@Lastname IS NULL OR @Lastname = '')))
189 BEGIN
190 SELECT @Description ='FirstName or LastName is Invalid'
191 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
192 END
193 ELSE
194 IF EXISTS (SELECT P.PersonId FROM AccountDB.licensing.Person (NOLOCK) P INNER JOIN AccountDB.licensing.OrganizationPerson (NOLOCK) O ON O.PersonId = P.PersonId
195 AND (P.eMail = @Email OR P.PhoenixId = @PhoenixID) AND O.OrganizationId <> @OrganizationId)
196 BEGIN
197 SELECT @Description ='Member already exists and attached to other organization'
198 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
199 END
200 ELSE
201 IF NOT EXISTS(SELECT CustomerID FROM Phoenix.dbo.Customer(Nolock) WHERE CUSTOMERID = @PhoenixID) AND (@Email is Null or @Email = '')
202 AND NOT EXISTS(SELECT PhoenixId FROM AccountDB.Licensing.Person(Nolock) WHERE PhoenixId = @PhoenixID) AND (@Email is Null or @Email = '')
203 BEGIN
204 SELECT @Description ='Invalid Customer ID'
205 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
206 END
207 ELSE
208 IF ((SELECT COUNT(P.Entry) FROM Phoenix.dbo.Phone P INNER JOIN Phoenix.dbo.Customer C ON C.Customer = P.Customer
209 WHERE P.Entry = (SELECT TOP 1 P.Entry FROM Phoenix.dbo.Phone P INNER JOIN Phoenix.dbo.Customer C ON C.Customer = P.Customer
210 WHERE C.CustomerID = @PhoenixID AND P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A')
211 AND P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A') > 1)
212 BEGIN
213 SELECT @Description ='Email ID attached with this Customer ID associated with other Customer IDs also'
214 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
215 END
216 ELSE
217 IF NOT EXISTS(SELECT TOP 1 P.Entry FROM Phoenix.dbo.Phone P INNER JOIN Phoenix.dbo.Customer C ON C.Customer = P.Customer INNER JOIN
218 Live.dbo.WebsiteUser (NOLOCK) W ON W.memberid = C.CustomerID WHERE P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A'
219 AND W.username = @email AND @Email IS NOT NULL AND @Email <> '' AND p.Entry =@Email) AND
220 EXISTS(SELECT W.memberid FROM live.dbo.WebsiteUser (Nolock) W WHERE W.username = @Email AND @Email IS NOT NULL AND @Email <> '')
221 BEGIN
222 DECLARE @WebsiteMemberID NCHAR(15)
223 SELECT @WebsiteMemberID = ''
224 SELECT @WebsiteMemberID = W.memberid FROM live.dbo.WebsiteUser (Nolock) W WHERE W.username = @Email AND @Email IS NOT NULL AND @Email <> ''
225 IF (@WebsiteMemberID IS NOT null)
226 BEGIN
227 SELECT @WebsiteMemberID = '(' + LTRIM(RTRIM(@WebsiteMemberID)) + ')'
228 SELECT @Description ='WebsiteUser already exists with this Email ID having Customer ID ' + @WebsiteMemberID
229 end
230 UPDATE #TmpBatchUploadUser SET IsUserProcessed = 0, IsAssignable = 0, Description = @Description WHERE Number = @Counter
231 END
232 ELSE
233 IF (@Description = '' OR @Description IS null)
234 BEGIN
235 SELECT @BUFirstName = @FirstName , @BULastName = @Lastname, @BUEmail = @Email , @BUPhoenixID = @PhoenixID;
236 SELECT TOP 1 @Email = [Entry], @FirstName = I.FirstName + ' ' + I.MiddleName , @Lastname = I.LastName, @PhoenixID = C.customerid
237 FROM Phoenix.dbo.Phone (NOLOCK) P
238 INNER JOIN Phoenix.dbo.Customer (NOLOCK) C ON C.Customer = P.Customer
239 INNER JOIN Phoenix.dbo.Individual (NOLOCK) I ON C.Customer = I.Customer
240 WHERE P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A' AND
241 (P.ENTRY = @Email OR C.customerid = @PhoenixID)
242 ORDER BY C.CUSTOMERID;
243 IF ( @Email = '' OR @PhoenixID = 0 OR @PhoenixID IS NULL) --Not Exist in Phoenix
244 BEGIN
245 PRINT 'not exists in phoenix'
246 SELECT @Description = '', @IsProcessed = 1, @IsAssignable=1;
247 SELECT @CreateWebsiteUserAndPhoenixCustomerInBulk = 1;
248 SELECT @needtocheckinEnterprise = 1;
249 END
250 ELSE
251 BEGIN
252 IF ( @Email <> '' AND @PhoenixID > 0 ) --May Not Exist in Phoenix
253 BEGIN
254 PRINT '--May Not Exist in Phoenix'
255 IF EXISTS ( SELECT [Entry] FROM Phoenix.dbo.Phone (NOLOCK) P INNER JOIN Phoenix.dbo.Customer (NOLOCK) C ON C.Customer = P.Customer WHERE [Entry]= LTRIM(RTRIM(@Email)) AND C.CustomerID = @PhoenixID AND PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A' )
256 BEGIN -- Exist in Phoenix
257 SELECT @Description = '', @IsProcessed = 0,@IsAssignable=1;
258 SELECT @needtocheckinEnterprise = 1;
259 END
260 ELSE --Not Exist in Phoenix
261 BEGIN
262 SELECT @Description = '', @IsProcessed = 1, @IsAssignable=1;
263 SELECT @CreateWebsiteUserAndPhoenixCustomerInBulk = 1;
264 SELECT @needtocheckinEnterprise = 1;
265 END
266 END
267 END
268 -- --Get the phoenix id if phoenix is null and phoenixid is generated through SP CreateWebsiteUserAndPhoenixCustomer
269 --IF ( (@PhoenixID = 0 OR @PhoenixID IS NULL) AND (@Email IS NOT NULL OR @Email <> ''))
270 --BEGIN
271 --SELECT @PhoenixID = C.CustomerID FROM Phoenix.dbo.Customer (NOLOCK) C INNER JOIN Phoenix.dbo.phone(NOLOCK) P ON P.Customer = C.Customer
272 --WHERE P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A' AND
273 -- P.ENTRY = LTRIM(RTRIM(@Email))
274 -- END
275 UPDATE #TmpBatchUploadUser SET IsUserProcessed = @IsProcessed, IsAssignable = @IsAssignable, Description = @Description WHERE Number = @Counter
276 if (@CreateWebsiteUserAndPhoenixCustomerInBulk = 1)
277 BEGIN
278 --Check for duplicate value in excel, if exists then not insert
279 IF NOT EXISTS(SELECT Email FROM #TmpCreateUser WHERE Email = @Email)
280 BEGIN
281 INSERT INTO #TmpCreateUser VALUES (NEWID(), NEWID(),@FirstName,@LastName,@Email,@Password)
282 END
283 END
284 IF(@needtocheckinEnterprise = 1)
285 BEGIN
286 PRINT '@needtocheckinEnterprise = 1)'
287 SELECT @UserCounter = @UserCounter + 1
288 IF NOT EXISTS(SELECT Email FROM #TmpUserEnterprise WHERE Email = @Email)
289 BEGIN
290 INSERT INTO #TmpUserEnterprise VALUES (@UserCounter, @Counter, @PhoenixID, @FirstName,@LastName,@Email,@Password)
291 END
292 END
293 END;
294 SELECT @Counter = @Counter + 1;
295 PRINT 'Counter ' + CONVERT(VARCHAR(20),@Counter)
296 END
297 --Start Insert all new records in Phoenix database
298 SELECT * INTO #TmpCreateWebsiteUser FROM #TmpCreateUser WHERE Email NOT IN (SELECT username FROM Live.dbo.WebsiteUser (NOLOCK))
299 AND Email NOT IN (SELECT [Entry] from phoenix.dbo.phone (nolock) WHERE phonetype = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A')
300 BEGIN
301 INSERT INTO Phoenix.dbo.Customer (Customer, [Name], Individual, TaxID)
302 select Customer, FirstName + ' ' + LastName, 1, '' FROM #TmpCreateWebsiteUser
303
304 INSERT INTO Phoenix.dbo.Individual (Customer, FirstName, MiddleName, LastName, Suffix)
305 select Customer, FirstName, '', LastName, '' FROM #TmpCreateWebsiteUser
306
307 INSERT INTO Phoenix.dbo.CustomerAddress (Customer, [Address], AddressType, [Primary])
308 SELECT Customer, [Address], 'BBF8D355-D3DF-4A0F-8AAE-C83C0A788AAF' , 1 FROM #TmpCreateWebsiteUser
309
310 INSERT INTO Phoenix.dbo.Address([Address], Street, City, Region, PostalCode, OverrideName, AdditionalName)
311 select [Address], '', '', '11111111-1111-1111-1111-111111111111', '', NULL, NULL FROM #TmpCreateWebsiteUser
312
313 INSERT INTO Phoenix.dbo.Phone (Customer, PhoneType, [Entry], [Primary], Priority)
314 SELECT [Customer], '5E9321F1-7791-4DD6-A3D0-2879DC9347CB', '', 1, 1 FROM #TmpCreateWebsiteUser
315
316 INSERT INTO Phoenix.dbo.Phone (Customer, PhoneType, [Entry], [Primary], Priority)
317 SELECT [Customer], '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A', Email, 0, 1 FROM #TmpCreateWebsiteUser WHERE Email <> ''
318
319 INSERT INTO Live.dbo.WebsiteUser (username, [password],memberid) SELECT U.Email, U.[Password], Phoenix.dbo.CustomerID(C.CustomerID)
320 FROM #TmpCreateWebsiteUser U INNER JOIN Phoenix.dbo.Customer C ON C.Customer = U.Customer
321
322 UPDATE P SET P.PhoenixId = C.CustomerID FROM AccountDB.Licensing.Person P INNER JOIN #TmpCreateWebsiteUser U ON P.eMail = U.Email
323 INNER JOIN Phoenix.dbo.Phone Ph ON U.Email = Ph.Entry INNER JOIN Phoenix.dbo.Customer C ON C.Customer = Ph.Customer WHERE Ph.PhoneType ='0D35AD34-A3DA-4C19-9463-2FA4CF51B43A'
324 END
325 --End Insert all new records in Phoenix database
326
327 --Start Insert all new records in AccountDB database
328 SELECT @Counter = 1
329 SELECT @TotRecord = COUNT(1)
330 FROM #TmpUserEnterprise
331
332 IF (@TotRecord > 0)
333 begin
334 WHILE (@Counter <= @TotRecord)
335 BEGIN
336 SELECT @IsProcessed = 0 ,
337 @IsAssignable = 0 ,
338 @Description ='',
339 @PersonId = NULL,
340 @PhoenixID = NULL,
341 @FirstName ='',
342 @Lastname='',
343 @Email='',
344 @UserCounter = 0
345
346 SELECT @UserCounter = UserCounter,
347 @Number = Number ,
348 @PhoenixID = PhoenixID ,
349 @FirstName = FirstName ,
350 @Lastname = LastName ,
351 @Email = Email ,
352 @Password = [Password]
353 FROM #TmpUserEnterprise
354 WHERE UserCounter = @Counter;
355
356 SELECT TOP 1 @Email = [Entry], @FirstName = I.FirstName + ' ' + I.MiddleName , @Lastname = I.LastName, @PhoenixID = C.customerid
357 FROM Phoenix.dbo.Phone (NOLOCK) P
358 INNER JOIN Phoenix.dbo.Customer (NOLOCK) C ON C.Customer = P.Customer
359 INNER JOIN Phoenix.dbo.Individual (NOLOCK) I ON C.Customer = I.Customer
360 WHERE P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A' AND
361 (P.ENTRY = @Email OR C.customerid = @PhoenixID)
362 ORDER BY C.CUSTOMERID;
363 SELECT @PersonId = PersonId FROM AccountDB.Licensing.Person (NOLOCK) P WHERE P.eMail = @Email
364 IF (@PersonId IS NOT NULL)
365 BEGIN
366 SELECT @PersonId = PersonId FROM AccountDB.Licensing.Person (NOLOCK) P WHERE P.eMail = @Email AND (P.PhoenixId IS NULL OR P.PhoenixId = 0)
367 IF (@PersonId IS NOT null)
368 BEGIN
369 UPDATE P SET P.PhoenixId = C.CustomerID , P.FirstName = I.FirstName, P.LastName = I.Lastname FROM AccountDB.Licensing.Person (NOLOCK) P INNER JOIN Phoenix.dbo.Phone (NOLOCK) Ph ON P.eMail = ph.Entry AND ph.PhoneType ='0D35AD34-A3DA-4C19-9463-2FA4CF51B43A'
370 INNER JOIN Phoenix.dbo.Customer (NOLOCK) C ON C.Customer = Ph.Customer INNER JOIN Phoenix.dbo.Individual (NOLOCK) I ON I.Customer = C.Customer WHERE P.PersonID = @PersonId
371 SELECT @Description = '' , @IsProcessed = 1, @IsAssignable=1, @TotProcessed = @TotProcessed + 1;
372 END
373 END
374 ELSE
375 BEGIN
376 IF EXISTS ( SELECT PersonId FROM AccountDB.Licensing.Person (NOLOCK) P WHERE P.PhoenixId = @PhoenixID)
377 BEGIN
378 SELECT @Description = '' , @IsProcessed = 1, @IsAssignable=1, @TotProcessed = @TotProcessed + 1;
379 UPDATE AccountDB.Licensing.Person SET Email = A.Entry , FirstName = A.FirstName + ' ' + A.MiddleName, LastName = A.LastName
380 FROM (SELECT TOP 1 P.Entry, I.FirstName ,I.MiddleName, I.LastName FROM Phoenix.dbo.Customer (NOLOCK) C INNER JOIN Phoenix.dbo.phone(NOLOCK) P ON P.Customer = C.Customer
381 INNER JOIN Phoenix.dbo.Individual (NOLOCK) I ON C.Customer = I.Customer WHERE P.PhoneType = '0D35AD34-A3DA-4C19-9463-2FA4CF51B43A' AND
382 C.CustomerID = @PhoenixID ORDER BY CustomerID ASC) A WHERE AccountDB.Licensing.Person.PhoenixId = @PhoenixID
383 END
384 ELSE
385 BEGIN
386 SELECT @Description = '' , @IsProcessed = 1, @IsAssignable=1, @TotProcessed = @TotProcessed + 1;
387 SELECT @PersonId = NEWID();
388 INSERT INTO AccountDB.Licensing.Person VALUES(@PersonId ,@FirstName, NULL , @Lastname , @Email , @Password , @PhoenixID );
389 INSERT INTO AccountDB.Licensing.OrganizationPerson VALUES(NEWID() ,@PersonId ,CONVERT(UNIQUEIDENTIFIER, @OrganizationId) );
390 END
391 END
392 UPDATE #TmpBatchUploadUser SET IsAssignable =@IsAssignable, IsUserProcessed=@IsProcessed, Description = @Description WHERE Number = @Number
393 SELECT @Counter = @Counter + 1
394 END
395 END
396 --End Insert all new records in AccountDB database
397 --Update the batchupload physical table with temp table
398 UPDATE B SET B.IsAssignable = Tmp.IsAssignable, B.IsUserProcessed = Tmp.IsUserProcessed, B.IsUserAssigned = Tmp.IsUserAssigned,
399 B.Description = Tmp.Description FROM AccountDB.Licensing.BatchUploadUser B INNER JOIN
400 #TmpBatchUploadUser Tmp ON B.Number = Tmp.Number
401 --SELECT * FROM AccountDB.Licensing.BatchUploadUser WHERE IsAssignable = 1 AND [User] = @MemberID ORDER BY Number;
402 SELECT @TotProcessed
403 DROP TABLE #TmpBatchUploadUser;
404 DROP TABLE #TmpCreateUser;
405 DROP TABLE #TmpUserEnterprise;
406 DROP TABLE #TmpCreateWebsiteUser;
407 DROP TABLE #TmpDuplicate
408 COMMIT;
409 END TRY
410
411 BEGIN CATCH
412 PRINT 'Catch in Licensing.BatchUploadUserCreation';
413 ROLLBACK;
414 SET NOCOUNT ON
415 --DELETE FROM AccountDB.Licensing.[BatchUploadUser]
416 -- WHERE [User] = @MemberID; -- Delete the batch upload data so that next time it will insert fresh data into this table for specific user
417 IF OBJECT_ID('tempdb..#TmpBatchUploadUser') IS NOT NULL
418 BEGIN
419 DROP TABLE #TmpBatchUploadUser;
420 END
421 IF OBJECT_ID('tempdb..#TmpCreateUser') IS NOT NULL
422 BEGIN
423 DROP TABLE #TmpCreateUser;
424 END
425 IF OBJECT_ID('tempdb..#TmpUserEnterprise') IS NOT NULL
426 BEGIN
427 DROP TABLE #TmpUserEnterprise;
428 END
429 IF OBJECT_ID('tempdb..#TmpCreateWebsiteUser') IS NOT NULL
430 BEGIN
431 DROP TABLE #TmpCreateWebsiteUser ;
432 END
433 IF OBJECT_ID('tempdb..#TmpDuplicate') IS NOT NULL
434 BEGIN
435 DROP TABLE #TmpDuplicate ;
436 END
437 --Log the Exception
438 DECLARE @Url AS VARCHAR(100)
439 DECLARE @AdditionalInformation AS VARCHAR(MAX)
440 DECLARE @ExceptionMessage AS VARCHAR(MAX)
441 SELECT @Url = ERROR_PROCEDURE() ,
442 @AdditionalInformation = 'Error line: '
443 + CONVERT(VARCHAR, ERROR_LINE()) ,
444 @ExceptionMessage = ERROR_MESSAGE()
445 PRINT @ExceptionMessage
446 EXEC [APAC].[Operations].[InsertLogEnrty]
447 4 ,
448 40 ,
449 @Url ,
450 @AdditionalInformation ,
451 @ExceptionMessage ,
452 NULL
453 END CATCH;
454 END;
455GO