[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

SQL 'IN' Statment Issue

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'
etc...

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

HELP!

Thanks,

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

Open in new window

0
Crazy_Penguins
Asked:
Crazy_Penguins
  • 4
  • 3
  • 2
  • +3
1 Solution
 
jamesguCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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:
http://crossedlogic.blogspot.com/2008/09/converting-delimited-string-to-separate.html

Here is another from EE:
http:Q_23786715.html
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@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):
archive,open



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))
AS  
begin
    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) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sm394Commented:
try that

Select * From Tickets
WHERE  (charindex(rtrim(Tickets.Status ),@Status) > 0)
0
 
Crazy_PenguinsAuthor Commented:
sm394 wins - implemented the fastest / most easy.

0
 
Crazy_PenguinsAuthor Commented:
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.
0
 
jazzIIIloveCommented:
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:
SELECT *
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:

SELECT *
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);
theConn.dbConn.commit();
stmt.close();

Above the number of params known...

Best regards...

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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:

archive
archived
open
not open

and you pass
archive,open

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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
sm394Commented:
Just in case if you want to apply In Clause on IDs or exact values

DECLARE @SearchIn XML
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)
                                                                   )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or simply modify slighty your previous syntax:
Select * From Tickets
WHERE  (charindex(rtrim( ',' + Tickets.Status + ',' ), ',' + @Status + ',') > 0)

Open in new window

0
 
sm394Commented:
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)
                                                                   )
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now