SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateLicenseUserGroupUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateLicenseUserGroupUsers]
GO

-- ====================================================  
-- Author:  Magic Software  
-- Create date: 14-Feb-2018  
-- Description: To insert or update a user group users of a license
-- ====================================================  
create PROCEDURE [dbo].[usp_UpdateLicenseUserGroupUsers] 
	-- Add the parameters for the stored procedure here
	@UserGroupId int, @UserIds varchar(2000), @Status bit out
AS
BEGIN
SET NOCOUNT ON;  

DECLARE @pos INT, @tempUserId int;
DECLARE @len INT;
DECLARE @value varchar(10), @value1 varchar(10);

if(@UserIds != '') 
begin
	set @UserIds = @UserIds + ',';
end

 set @Status = 0;  
 BEGIN TRY  
  BEGIN TRANSACTION  


			set @pos = 1
			set @len = 0

			WHILE CHARINDEX(',', @UserIds, @pos)>0
			BEGIN
				set @len = CHARINDEX(',', @UserIds, @pos) - @pos;
				set @value = SUBSTRING(@UserIds, @pos, CHARINDEX('-', @UserIds, @pos) - @pos);
				set @value1 = SUBSTRING(@UserIds, CHARINDEX('-', @UserIds, @pos) + 1, @len - len(@value) - 1);
				select @pos, @len, @value, @value1;
				set @tempUserId = convert(int, @value);
				if(exists(select * from UserGroupToAIAUser where UserGroupId = @UserGroupId and UserId = @tempUserId))
				begin
					if(@value1 = '0')
					begin
						delete from UserGroupToAIAUser where UserGroupId = @UserGroupId and UserId = @tempUserId;
					end
				end
				else
				begin
					if(@value1 = '1')
					begin
						insert into UserGroupToAIAUser(UserGroupId, UserId) values(@UserGroupId, @tempUserId);
					end
				end
				set @pos = CHARINDEX(',', @UserIds, @pos+@len) + 1;
			END

  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