SQL how to get an equality comparator on a list of ints

Question

I'm writing an SQL query as follows:

ALTER proc [dbo].[Invoice_GetHomePageInvoices] (
      @AreaIdList varchar(max)
,      @FinancialYearStartDate datetime = null
,      @FinancialYearEndDate datetime = null
) as

    set nocount on

    select *
    from Invoice i
    left outer join Organisation o on i.OrganisationId = o.Id
    left outer join Area a on i.AreaId = a.Id
where i.InvoiceDate BETWEEN @FinancialYearStartDate AND @FinancialYearEndDate

The @AreaIdList parameter is going to be in the format "1,2,3" etc.

I'm wanting to add a line which will only return invoices who have area id equal to any of the ids in @AreaIdList.

I know how to do a statement if it was on areaId to search on ie. where i.AreaId == areaId problem is now I have this list I got to compare for every area Id in @AreaIdList.

Can anybody tell me how you would go about this?

Answer 1

Unpack your ID list to a table and use where AreadID in (select ID from ...)

ALTER proc [dbo].[Invoice_GetHomePageInvoices] (
       @AreaIdList varchar(max)
,      @FinancialYearStartDate datetime = null
,      @FinancialYearEndDate datetime = null
) as

set nocount on

set @AreaIdList = @AreaIdList+','
declare @T table(ID int primary key)
while len(@AreaIdList) > 1
begin
  insert into @T(ID) values (left(@AreaIdList, charindex(',', @AreaIdList)-1))
  set @AreaIdList = stuff(@AreaIdList, 1, charindex(',', @AreaIdList), '')
end

select *
from Invoice i
  left outer join Organisation o on i.OrganisationId = o.Id
  left outer join Area a on i.AreaId = a.Id
where i.InvoiceDate BETWEEN @FinancialYearStartDate AND @FinancialYearEndDate and
      i.AreadID in (select ID from @T)