usp_UpdateLicenseModuleStatus.sql
2.28 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
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateLicenseModuleStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateLicenseModuleStatus]
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 06-Feb-2018
-- Description: To insert or update the module status on or off for a license
-- ====================================================
CREATE PROCEDURE [dbo].[usp_UpdateLicenseModuleStatus]
@LicenseId int, @ModuleStatusText varchar(2000), @Status bit out
AS
BEGIN
DECLARE @sItemDelimiter CHAR(1)
DECLARE @sitem VARCHAR(100)
DECLARE @sRecordDelimiter CHAR(1)
DECLARE @cModuleStatuses CURSOR
set @sRecordDelimiter = '|';
SET @sItemDelimiter = '-'
DECLARE @ModuleId INT
Declare @ModuleStatus bit
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @Status = 0;
BEGIN TRY
BEGIN TRANSACTION
-- get the module id and module status
SET @cModuleStatuses = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@ModuleStatusText, @sRecordDelimiter)
-- loop through the cursor to insert or update the individual module status
OPEN @cModuleStatuses
FETCH NEXT FROM @cModuleStatuses INTO @sitem
WHILE @@FETCH_STATUS = 0
BEGIN
set @ModuleId = convert(int, SUBSTRING(@sitem, 1, CHARINDEX(@sItemDelimiter,@sitem) - 1));
set @ModuleStatus = convert(bit, SUBSTRING(@sitem, CHARINDEX(@sItemDelimiter, @sitem) + 1, len(@sitem) - CHARINDEX(@sItemDelimiter, @sitem)));
if(exists(select * from ModuleToLicense where ModuleId = @ModuleId and LicenseId = @LicenseId))
begin
UPDATE ModuleToLicense SET Status = @ModuleStatus where ModuleId = @ModuleId and LicenseId = @LicenseId;
end
else
begin
insert into ModuleToLicense(LicenseId, ModuleId, Status) values(@LicenseId, @ModuleId, @ModuleStatus);
end
FETCH NEXT FROM @cModuleStatuses INTO @sitem
END
CLOSE @cModuleStatuses
DEALLOCATE @cModuleStatuses
COMMIT TRANSACTION
set @Status = 1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO