SQL Alternative to IN operator with variable and between

Question

I'm trying to put some variables to an IN Operator but unfortunately it's not working. (found some articles on the internet about it). So what can I do?

I have some stacked queries combined to one. But then I have several times the same argument, well that's no problem. But I have to change all the arguments every times I want to make a new search. So to make it easy I used variables at the top of the query so I can't forget even one to change. And that's working also except for the IN operator. Somehow you can't use multiple variables with the IN operator except when using Dynamic SQL.

Now I have:

where [Item No_] not in ('0000900','00009900','00009906')

to exclude the products I don't want to see. At this moment there are only 3 but it have to be more, about 20 single once and some are in a range like 00005100 - 00007500 (yes that are 2400). (and in the next query I just need those 2400)

So I tried to Declare @Exception, but that's not working. Now I wonder if there is a way to make it work. If not with a variable then at least a combination of a IN and between statement. Or some other way around.

And here is the whole query. I commented out the @Exception variable:

Declare @Start datetime = '01.04.2015',
        @Ende datetime = '30.04.2015';
/*      @Ausnahme ;
Insert @Ausnahme values (00009000),(00009900),(00009906);
*/

SELECT t1.[Location Code], CAST(t1.Umsatz as DECIMAL(18,2))as Umsatz , CAST(COALESCE(t2.Ersatznachweis, 0) as DECIMAL(18,2)) as Ersatznachweis, CAST(T5.Rückgabe as Decimal(18,2)) as Rückgabe
FROM (
SELECT [Location Code],  SUM(WareBrutto) AS Umsatz
FROM (SELECT  DISTINCT [Location Code], [Document No_] , WareBrutto from [Item Ledger Entry] 
      WHERE [Location Code] between '0000' and '0040' and [Document Date] between @Start and @Ende) t
GROUP BY [Location Code]) as t1

LEFT JOIN
(select [Location Code], sum([Quantity]*Bruttopreis) as Ersatznachweis  from [Item Ledger Entry] 
where [Location Code] between '0000' and '0040' and [Item No_] not IN ('00009000','00009900','00009906') and Gutschrift = '1' and [Document Date] between @Start and @Ende
Group By [Location Code]) as t2 

on t1.[Location Code] = t2.[Location Code] 

Left JOIN
(Select [Location Code], sum(Quantity*PR_Brutto*-1) As Rückgabe
From ( 
select [Location Code], [Item No_], [Quantity] from [Item Ledger Entry] 
where [Location Code] between '0000' and '0040' and [Item No_] not in ('00009000','00009900','00009906') and Rückgabe = '1' and [Document Date] between @Start and @Ende ) as T3

Left Join 
(Select ARTNR, PR_Brutto from dep_prs
where LIdx = '4' and Preisgruppe = '1') as T4 ON T3.[Item No_] = T4.ARTNR
Group By T3.[Location Code]) as T5 on t1.[Location Code] = T5.[Location Code] 

Order by t1.[Location Code]

Answer 1

You can declare a table variable (or parameter if it's a part of a procedure or function) and use it for the not in part:

DECLARE @NotIn table (
    NotInValues int
)

INSERT INTO @NotIn Values
('00009000'),
('00009900'),
('00009906')

and use it in your code like this:

where [Location Code] between '0000' and '0040' 
and [Item No_] not IN (select NotInValues from @NotIn) 
and Gutschrift = '1' 
and [Document Date] between @Start and @Ende    

Note #1: for a large number of values, not exists will probably perform better then not in

Note #2: If it's a part of a stored procedure you will need to create a user defined table type, and use it to declare the table valued parameter. Also, table valued parameters are readonly, so performing DML statements (insert/update/delete) on them will raise an error.

To create the udt:

CREATE TYPE IntegerList As Table
(
    IntValue int
)

To declare it in the stored procedure parameters list:

CREATE PROCEDURE procedureName
(
   @IntList dbo.IntegerList READONLY
   -- Note that the readonly must be a part of the parameter declaration.
)