dbo.DeleteLicense.StoredProcedure.sql
6.1 KB
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[DeleteLicense] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- Create date: 24-Sep-2009
-- Description: to delete the license
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteLicense]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteLicense]
GO
CREATE PROCEDURE [dbo].[DeleteLicense]
@iLicenseId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @iLicenseTypeId TINYINT
DECLARE @cGetDetail CURSOR
DECLARE @iSiteId INT
DECLARE @iUserId INT
DECLARE @sErrorStatus CHAR(2)
SET @sErrorStatus = 'ok'
SET @iLicenseTypeId = (SELECT LicenseTypeId FROM License WHERE Id = @iLicenseId)
-- check if license is site license
IF @iLicenseTypeId = 3
BEGIN
-- delete records from tables which store information about building level account
SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT SiteId FROM SiteToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
OPEN @cGetDetail
FETCH NEXT FROM @cGetDetail INTO @iSiteId
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM AIAUserToSite WHERE SiteId = @iSiteId
DELETE FROM SiteToLicenseEdition WHERE SiteId = @iSiteId
DELETE FROM Site WHERE Id = @iSiteId
FETCH NEXT FROM @cGetDetail INTO @iSiteId
END
CLOSE @cGetDetail
END
-- delete records from tables which store information about user
SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT Userid FROM AIAUserToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
OPEN @cGetDetail
FETCH NEXT FROM @cGetDetail INTO @iUserId
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM AIAUserToLicenseEdition WHERE Userid = @iUserId
DELETE FROM LoginDetail WHERE UserId = @iUserId
DELETE FROM SessionManager WHERE UserId = @iUserId
DELETE FROM IncorrectLoginAttempts WHERE UserId = @iUserId
DELETE FROM UserGroupToAIAUser WHERE UserId = @iUserId
DELETE FROM AIAUser WHERE Id = @iUserId
FETCH NEXT FROM @cGetDetail INTO @iUserId
END
CLOSE @cGetDetail
-- delete records from tables which store information about the license
DELETE FROM UserGroup WHERE LicenseId = @iLicenseId
DELETE FROM LicenseToEdition WHERE LicenseId = @iLicenseId
DELETE FROM SingleUserDetail WHERE LicenseId = @iLicenseId
DELETE FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId
DELETE FROM License WHERE Id = @iLicenseId
COMMIT TRANSACTION
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END
GO