TableAdapter List params for IN clause-CodePudding

SQL Server, VS 2019 Winforms, ReportViewer.

This doesn't work in straight SQL either, so I'll start there. I have a query that looks like this:

declare @d1 Date, @d2 date, @SalesRep nvarchar(20), @customer nvarchar(300), @stype nvarchar(10)
    
    set @d1 = '01 oct 2021'
    set @d2 = getdate()
    set @SalesRep = ''
    set @customer = ''''',''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'''
    set @sType=null
    
    select @customer
    
    select 
        vss.Customer, vss.Invoice_No, vss.Salesman, 
        vss.Invoice_Date, vss.Total_Amount, vss.Description 
    from 
        v_SaleSum vss
    where 
        vss.Invoice_Date between @d1 and @d2
        and ((ISNULL(@SalesRep,'') = '') OR (vss.Salesman = @SalesRep))
        **and (Customer in (@customer))**
        and [dbo].[Invoice_sType] (vss.Invoice_No, @sType)=1
    Order by 
        vss.Invoice_No

You can see the crux of the issue here. The @customer param needs to be a list. But if you comma separate it, as I have the query looks for customers that match the whole comma separated list as one string, not as a list of strings.

I do not have the option of modifying the SQL at runtime because of the whole Dataset, BindingSource, TableAdapter setup.

What am I missing here? There must be some way to add a list of values to a parameter.

--Update: Here's why I can't use Table-Value Parameters: There is no "Table" parameter type supported.

CodePudding user response：

I found that I could do this:

... and (@customer like ('%'''  Customer '''%'))

Since the match string is in the format '''A'',''B''' (or more simply without having to escape the tick marks, ' 'A','B' '

searching for the customer name to exist in the parameter seems kind of backwards, but its working.

This way, I check if %'A'% is like 'A','B','C'