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