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);

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

 set @Status = 0;  
 BEGIN TRY  
  BEGIN TRANSACTION  

			delete UGU from UserGroupToAIAUser UGU where UserGroupId = @UserGroupId;

			set @pos = 0
			set @len = 0

			WHILE CHARINDEX(',', @UserIds, @pos+1)>0
			BEGIN
				set @len = CHARINDEX(',', @UserIds, @pos+1) - @pos;
				set @value = SUBSTRING(@UserIds, @pos, @len);
				set @tempUserId = convert(int, @value);
				insert into UserGroupToAIAUser(UserGroupId, UserId) values(@UserGroupId, @tempUserId);
				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