IN Clause with WHERE clause not getting proper results in SQL Server

Question

 SELECT Col1, Col2, Col3, Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 BETWEEN @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (CASE @InvoiceMethod //Problem is Here 
                  WHEN ''
                    THEN def
                  ELSE (@InvoiceMethod)
               END)

A piece of code from the stored procedure. If am executing this, it's not returning any rows, even though it has some to return. Problem is with the IN clause, if I didn't pass anything to IN clause i.e @InvoiceMethod is null, then I'm getting rows.

If I pass anything to @InvoiceMethod, I'm not getting any rows.

The value in @InvoiceMethod is = 'A','B'

I tried many combinations like 'A','B' or "A","B" without any results.

How to pass values to IN clause please? In which format?

Please help me out of this.

Modified the stored procedure to the following,

Declare @tmpt table (value nvarchar(5) not null)
      SET @InvoiceCount=(select COUNT(*) from dbo.fnSplit(@InvoiceMethod, ','))
    SET @tempVar=1;
    WHILE @tempVar<=(@InvoiceCount)
          BEGIN
        INSERT INTO @tmpt (value) 
        VALUES (@InvoiceMethod);//Here i need to insert array of values to temp   table.like invoicemethod[0],invoicemethod[1]&invoicemethod[2] depends on @InvoiceCount

        SET @tempVar=@tempVar+1;
      END
--DECLARE @tmpt TABLE (value NVARCHAR(5) NOT NULL)
--INSERT INTO @tmpt (value) VALUES (@InvoiceMethod);

 SELECT Col1,Col2,Col3,Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 between @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (SELECT value FROM @tmpt)

But not getting the results as expected :(

To what value are you trying to compare? @.InvoiceMethod = 'A' or @.InvoiceMethod = 'A','B' — Vishwanath Dalvi, May 08 '14 at 04:56
What are you trying to achieve with that expression inside `IN` clause? — Mat J, May 08 '14 at 04:57
@user1676709 Try to execute In clause separately and see what result are you getting it back. — Vishwanath Dalvi, May 08 '14 at 04:59
to IN clause am passing 'A','B' both.With that am trying to filter the data.I will get some records,in that records i need only the records which has A & B in it,not all the records. — Lalita, May 08 '14 at 05:01
I'm pretty sure you can't use the `IN` clause with a variable as you're trying to do. See http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable — hatchet - done with SOverflow, May 08 '14 at 05:04
@ Hatchet am trying to achieve this : Eg; Select * from table1 where col1 IN('India','US','Singapore').But Col1 has 10 country names in it.I need only what i have mentioned in the IN clause — Lalita, May 08 '14 at 05:11
@user1676709: See my answer and the other one also..You cant use it like this. Check MSDN also. you should put your values in a temp table or something and use inline select afterwards — e4rthdog, May 08 '14 at 05:32

score 2 · Answer 1 · edited May 23 '17 at 12:15

2

IMO this isn't a good way to approach this problem, by passing a list of filter values for a column in a comma separated string, as this is almost encouraging a Dynamic Sql approach to the problem (i.e. where you EXEC a built Sql string which pastes in the @InvoiceMethod as a string).

Instead, Sql 2008 has Table Valued Parameters, (and prior to this, you could use Xml), which allows you to pass structured data into a procedure in a table format.

You then just need to join to this table parameter to effect the 1..N valued IN () filtering.

CREATE TYPE ttInvoiceMethods AS TABLE
(
  Method VARCHAR(20)
);
GO

CREATE PROCEDURE dbo.SomeProc 
(
   @InvoiceMethod ttInvoiceMethods READONLY, -- ... Other Params here
)
AS
begin

   SELECT Col1, Col2, ...
   FROM Table1
        INNER JOIN @InvoiceMethod
           ON Table1.def = @InvoiceMethod.Method -- Join here
   WHERE User1 = @Owner
        ... Other Filters here

END

Have a look here for a similar solution with a fiddle.

Edit

The optional parameter (@InvoiceMethod = '') can be handled by changing the JOIN to the TVP with a subquery:

   WHERE
     --  ... Other filters
     AND (Table1.def IN (SELECT Method FROM @InvoiceMethod))
        OR @InvoiceMethod IS NULL)

To Initialize a TVP to NULL, just don't bind to it in C# at all.

edited May 23 '17 at 12:15

Community

1
1

answered May 08 '14 at 05:11

StuartLC

104,537
17
209
285

Actually @InvoiceMethod is dynamic,like Eg; Select * from table1 where col1 IN('India','US','Singapore').But Col1 has 10 country names in it.I need only what i have mentioned in the IN clause. – Lalita May 08 '14 at 05:29
I have checkbox list in the application which has 10 item in it,but user selects according to his choice.What ever user selects am passing to SP to IN clause – Lalita May 08 '14 at 05:35
Yes, we get that, and also as per your OP, the case where the user selects nothing seems to mean that the filter is to be ignored altogether (i.e. all possible values of @InvoiceMethod are to be used). As a short term solution, look at e4rthdog's suggestion (unpacking the values into a table variable and joining to it). But as a stronger pattern, look at passing structured data like Table Valued Parameters from the outset. – StuartLC May 08 '14 at 05:40
Thank you Stuart ,Actually am new to TVP.So confused.Let me try the solution proposed by you. – Lalita May 08 '14 at 05:49
In that case, I would suggest you start with e4rthdogs approach first, as it addresses the crux of the issue and doesn't require changing your proc and C# code. TVP will require change to your c# code, as you will need to populate a DataTable or other Structure. – StuartLC May 08 '14 at 05:52

score 1 · Answer 2 · edited May 08 '14 at 08:27

1

I think a variable represetning multiple values with comma is not allowed in the in clause. You should either use string fiunctions (split and join) or go with the temp table solution. I prefer the second.

Use a temporary table to store your values and then pass it to your in statement

DECLARE @tmpt TABLE (value NVARCHAR(5) NOT NULL)

INSERT INTO @tmpt .........
...
...

 SELECT Col1,Col2,Col3,Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 BETWEEN @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (SELECT value FROM @tmpt)

edited May 08 '14 at 08:27

Kevin Hogg

1,771
25
34

answered May 08 '14 at 05:03

e4rthdog

5,103
4
40
89

I have modified the SP according to your solution,But not getting the results :( plz take a look @ the modified Question – Lalita May 08 '14 at 06:20
Can you provide an output of a select in the tmp table? Invoicemethod should not be like 'cc','yy','zz'..you should insert in the tmp table one by one the values, 'cc' 'yy' 'zz'..you have to iterate for every distinct value of the invoicemethod..can you please tell us how you calculate the @invoicemethod? – e4rthdog May 08 '14 at 07:01
Since am new to temp tables.Can you plz provide me some links or samples on how to insert data/multiple records into Temptables.SO that it will be very helpful for me.I hav searched in Net,but couldnt get proper information. – Lalita May 14 '14 at 09:00
I have modified the question.Am not able to insert multiple records into temp tables. – Lalita May 14 '14 at 10:04

score 1 · Accepted Answer · answered May 16 '14 at 18:52

Used Splitfunctions to resolve the issue,Modified SQL Query

SELECT Col1, Col2, Col3, Col4
 FROM Table1 
  WHERE User1 = @Owner
AND group1 = @Group
AND date1 BETWEEN @startDate AND @endDate
AND Mail LIKE @email
AND def IN (SELECT * FROM sptFunction(@InvoiceMethod,',')) //Problem is Here (Solved by using split functions)

IN Clause with WHERE clause not getting proper results in SQL Server

3 Answers3