CREATE procedure dbo.proc_ProcessClientSalesReps
	@ClientUID char(32)
	, @SalesRepID varchar(100)
as
	declare @Error int
		, @ClientID int
	
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	select
		@ClientID = Client.ClientID
	from
		Client
	where
		(Client.ClientUID = dbo.FormatGUID(@ClientUID))
	
	declare @TransactionCountOnEntry int
	
--Loop thru the @SalesRepID to grab each ID then process them accordingly
if @ErrorCode = 0
begin
	declare @SalesRepsID int
		, @Count int
		, @CurSalesRepID int
		, @Pos int
	
	select @Count = 0
	select @SalesRepID = ltrim(rtrim(@SalesRepID)) + ','
	select @Pos = charindex(',', @SalesRepID, 1)
	
	if replace(@SalesRepID, ',', '') <> ''
	begin
		while @Pos > 0
		begin
			select @CurSalesRepID = ltrim(rtrim(left(@SalesRepID, @Pos - 1)))
			if @CurSalesRepID <> ''
			begin
-- Here i'd like to run a sproc that runs an update sproc to update the reps that are already listed with the client
--  then another to add any reps that are in the list, but not already in the db.

-- I understand that here I am splitting up the IDs in @SalesRepID, but i'd like to eliminate this but just sending the whole lst to a query if possible.

-- This one adds, but does not check first to see if they already exist.
--				exec @Error = dbo.proc_AddClientSalesRep @ClientID, @CurSalesRepID, @SalesRepsID output
				if @Error = 0
					select @Count = @Count + 1
				else
					break
			end
			select @SalesRepID = right(@SalesRepID, len(@SalesRepID) - @Pos)
			select @Pos = charindex(',', @SalesRepID, 1)
		end
		
		select @ErrorCode = @Error
	end
end

if @ErrorCode = 0
begin
	if @@Error <> 0
		select @ErrorCode = @@Error
end

if @ErrorCode = 0
begin
	if @Count < 1
		select @ErrorCode = 1
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode

GO

CREATE procedure dbo.proc_AddClientSalesRep
	@ClientID int
	, @SalesRepID int
	, @ClientSalesRepID int output
as
	declare @ErrorCode int
	select @ErrorCode = @@Error
	
	declare @TransactionCountOnEntry int
	
if @ErrorCode = 0
begin
	select @TransactionCountOnEntry = @@TranCount
	begin transaction
		insert
			ClientSalesRep
			(
			ClientID
			, SalesRepID
			)
		values
			(
			@ClientID
			, @SalesRepID
			)
		
		select @ClientSalesRepID = @@Identity, @ErrorCode = @@Error
end

if @@TranCount > @TransactionCountOnEntry
begin
	if @ErrorCode = 0
		commit transaction
	else
		rollback transaction
end

return @ErrorCode


GO

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

CREATE TABLE [dbo].[ClientSalesRep] (
	[ClientSalesRepID] [int] IDENTITY (1, 1) NOT NULL ,
	[ClientID] [int] NOT NULL ,
	[SalesRepID] [int] NOT NULL ,
	[SalesRepEffectiveDate] [datetime] NOT NULL ,
	[SalesRepEndDate] [datetime] NULL ,
	[SalesRepActive] [bit] NOT NULL 
) ON [PRIMARY]
GO
drop table [dbo].[ClientSalesRep]
GO

declare @id varchar(100)
select @id = '1,2'

select
	*
from
	clientsalesrep
where
	clientid = 1
	and salesrepid not in (select @id)

-- Add new reps
INSERT ClientSalesRep ( column list )
SELECT Column1, Column2 ...
FROM  dbo.MySplitter(@SalesRepID) REP
      LEFT OUTER JOIN ClientSalesRep CSR
           ON CSR.SalesRepID = REP.ID
          AND CSR.ClientID = @ClientUID
WHERE CSR.SalesRepID IS NULL -- Just the "missing" ones

-- Inactivate "stale" reps
UPDATE U
SET   SalesRepActive = 0
FROM  ClientSalesRep U
      LEFT OUTER JOIN dbo.MySplitter(@SalesRepID) REP
          ON REP.ID = U.SalesRepID
WHERE     U.ClientID = @ClientUID
      AND REP.ID IS NULL -- SalesRep not in current list