Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL 'IN' Statment Issue

Posted on 2008-11-01
Medium Priority
Last Modified: 2013-11-08
I am using the SQL Query Builder in VS08 - trying to get an IN statement to work - I put what I have come up with below.

I have tried putting everything I can think of in for @Status
'archive', 'open'
archive, open
'archive' 'open'

just archive (no quotes) seems to work in the Query Builder, but I need to select 3-4 status levels.\



Select * From Tickets Where Tickets.Status IN (@Status)

Open in new window

Question by:Crazy_Penguins
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3

Expert Comment

ID: 22859128
in java, you can only bind known number of parameters, like ... in (?, ?,?) in case you have 3 parameters. This should be same in other languages

or you can build the whole sql string (like Select * From Tickets Where Tickets.Status IN ('archive', 'open')
 ) without parameter binding
LVL 60

Expert Comment

by:Kevin Cross
ID: 22859132
The problem is that the IN statement is looking for an array of values and you are passing one value that is comma delimited.

You will need to write a split function and then do this:

SELECT * FROM Tickets WHERE Tickets.Status IN (SELECT entry FROM dbo.split(@Status, ','))

Here is an example of split functions:

Here is another from EE:
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22859137
@Status is considered 1 single value, and not a list of values, which is the reason why it "does not work".

please create the below function, it will come in handy several times in your future, probably:

with @status having the value (no quotes, not spaces):

Select * From Tickets Where Tickets.Status IN (select value from dbo.ParmsTolist(@Status,',') )
create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
     DECLARE @TempList table
          Value varchar(8000)
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
     DECLARE @Value varchar(8000), @Pos int
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
          WHILE @Pos > 0 --and @Loops < 100
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
     INSERT @result
     SELECT value
        FROM @TempList

Open in new window

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

LVL 13

Accepted Solution

sm394 earned 2000 total points
ID: 22859160
try that

Select * From Tickets
WHERE  (charindex(rtrim(Tickets.Status ),@Status) > 0)

Author Closing Comment

ID: 31512389
sm394 wins - implemented the fastest / most easy.


Author Comment

ID: 22859182
sm394 wins - implemented the fastest / most easy.

I did also try mwvisa1 - could not get the function in - could be because I am using SQL 2000 on the back-end.
LVL 12

Expert Comment

ID: 22859200
Hi there;

The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria as my friends dealt with examples...Let me add to clarify the usage:

Suppose we have the below table:

Employee               Date         Hours
John Smith             5/6/2004      8
Allan Babel             5/6/2004     8
Tina Crown            5/6/2004      8
John Smith             5/7/2004       9
Allan Babel             5/7/2004      8
Tina Crown            5/7/2004      10
John Smith              5/8/2004       8
Allan Babel             5/8/2004       8
Tina Crown             5/8/2004       9    

Consider the following SQL query using the SQL IN clause:
FROM EmployeeHours
WHERE Date IN ('5/6/2004', '5/7/2004')

This SQL expression will select only the entries where the column Date has value of '5/6/2004' or '5/7/2004', and you can see the result below:

Employee Date Hours
John Smith 5/6/2004 8
Allan Babel 5/6/2004 8
Tina Crown 5/6/2004 8
John Smith 5/7/2004 9
Allan Babel 5/7/2004 8
Tina Crown 5/7/2004 10

We can use the SQL IN statement with another column in our EmployeeHours table:

FROM EmployeeHours
WHERE Hours IN (9, 10)

The result of the SQL query above will be:

Employee Date Hours
John Smith 5/7/2004 9
Tina Crown 5/7/2004 10
Tina Crown 5/8/2004 9

>>in java, you can only bind known number of parameters, like ... in (?, ?,?) in case you have 3 >>parameters. This should be same in other languages

Good point, jamesgu is right...
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

But not only you can create known number of parameter

Statement stmt;
String sql;    
int rows;      

sql = "INSERT INTO tCust " 
    + "(custId, custName, custAddr) "
    + "VALUES "
    + "('" + custId   + "',"
    + "('" + custName + "',"
    + "('" + custAddr + "')";

stmt = theConn.createStatement();
rows = stmt.executeUpdate(sql);

Above the number of params known...

Best regards...

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22859209
>sm394 wins - implemented the fastest / most easy.

except that you might run into issues... not on this concrete example, but in other cases.

say you have values in your column like:

not open

and you pass

as argument. the selected solution will return all 4 rows, although you actually only want 2 of them...

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22859214
second "problem": if you have an index on the field, the selected solution will NOT be able to use that index.
with the function suggestion of mvisa and me, that would still be possible, ie trying to avoid the full table scan...
LVL 13

Expert Comment

ID: 22859273
Just in case if you want to apply In Clause on IDs or exact values

SET @SearchIn ='<ROOT><ID>1</ID><ID>2</ID></ROOT>'

 Select * From Tickets Where Ticket.ID IN (
                                                                    SELECT  ParamValues.ID.value('.','int')
                                                                     FROM @SearchIn.nodes('//ID') as ParamValues(ID)
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22859297
or simply modify slighty your previous syntax:
Select * From Tickets
WHERE  (charindex(rtrim( ',' + Tickets.Status + ',' ), ',' + @Status + ',') > 0)

Open in new window

LVL 13

Expert Comment

ID: 22859313
In your case

declare @Statusxml;
set @Status=convert(xml,N'<Root><Status>archive</Status><Status>open</Status></Root>');

Select * From Tickets Where Ticket.Status IN (
                                                                    SELECT  ParamValues.Status.value('.','nvarchar(50)')
                                                                     FROM @Status.nodes('//Status) as ParamValues(Status)

Featured Post

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question