GetAccountFilterData

CREATE PROCEDURE [dbo].[GetAccountFilterData]
    @accountId nvarchar(100),
    @country nvarchar(100),
    @testType varchar(5),
    @associatedAccounts nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;

declare @associatedAccountsTable table (id int, data nvarchar(100))

insert into @associatedAccountsTable
select * from [dbo].[Split](@associatedAccounts, ',')

if @testType = 'static string'
begin

select * from (
select ri.ReaderSerialNumber as Value,
case
    when ri.readertitle is null or len(ri.readertitle) = 0 then ri.ReaderSerialNumber
    else ri.readertitle
end as Name
from ReaderInfo ri
inner join Asset ass on ri.ReaderSerialNumber = ass.SerialNumber
where ass.AccountNumber = @accountId
union
select ri.ReaderSerialNumber as Value,
case
    when ri.readertitle is null or len(ri.readertitle) = 0 then ri.ReaderSerialNumber
    else ri.readertitle
end as Name
from ReaderInfo ri
inner join MastitisTest mt on ri.ReaderSerialNumber = mt.ReaderSerialNumber
inner join Account a on a.AccountNumber = mt.AccountNumber
inner join CustomerLocation cl on mt.CustomerLocationID = cl.ID
left join Account ca on ca.AccountNumber = cl.ConsumingAccountNumber
where (a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(cl.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country)))
as Readers

-- i believe this is the problem query
select distinct [Batches].ID, [Batches].Notes, [Batches].StartTime, [Batches].CompleteTime, [Batches].ReaderSerialNumber as ReaderSN from TestBatch [Batches]
inner join MastitisTest mt on [Batches].ID = mt.BatchID
inner join Account a on a.AccountNumber = mt.AccountNumber
left join CustomerLocation cl on mt.CustomerLocationID = cl.ID
left join Account ca on ca.AccountNumber = cl.ConsumingAccountNumber
where (a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(cl.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country))

select distinct Locations.ID, Locations.Name, Locations.ReaderSerialNumber from CustomerLocation Locations
inner join MastitisTest mt on Locations.ID = mt.CustomerLocationID
inner join Account a on a.AccountNumber = mt.AccountNumber
left join Account ca on ca.AccountNumber = Locations.ConsumingAccountNumber
where Locations.ID <> '00000000-0000-0000-0000-000000000000' and ((a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(Locations.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country)))

end
else
begin
-- this else can be ignored
select 1
end
END


GO