Post reply

Using the IN operator with a variable

  • May 31, 2005 at 3:34 pm

    #67089

    I'm trying to understand how to pass a list of comma-separate values into my WHERE clause for use with an IN operator. Here's what it looks like, though no rows are being returned:

    DECLARE @List varchar(100)

    SET @List = 'ONA', 'TSV', 'KVS'

    SELECT *

    FROM table

    WHERE column IN(@List)

    I've also tried setting up my @List variable like this to deal with the commas:

    SET @List = 'ONA' + '','' + 'TSV' + '','' + 'KVS'

    However, I don't have any problems returning the appropriate data if I substitute the actual values in for my parameter like so:

    SELECT *

    FROM table

    WHERE column IN('ONA', 'TSV', 'KVS')

    OR

    SELECT *

    FROM table

    WHERE column IN('ONA' + '','' + 'TSV' + '','' + 'KVS')

    Any ideas would be appreciated!

  • May 31, 2005 at 3:52 pm

    #562047

    Try

    DECLARE @List varchar(100)

    SET @List = '''ONA'', ''TSV'', ''KVS'''

    Also, you will need to execute the entire statement as dynamic SQL.

    SELECT @List='SELECT * FROM Table WHERE Column IN ('+@List+')'

    SP_EXECUTESQL (@List)

    Brian

  • May 31, 2005 at 5:44 pm

    #562056

    Solution is good. I would like to do it another way.

    Please note Dynamic SQL will not work when the user has execute rights on a stored procedure and no select rights on a table.

    Another way split the parameter and insert them into a variable of table data type or a temp table. Then use that in the main query.

    Declare @params Table (Param VARCHAR (25))

    /* split the varchar parameter and insert into the table dynamically */

    Insert into @params VALUES ('ONA')

    Insert into @params VALUES ('TSV')

    Insert into @params VALUES ('KVS')

    SELECT * FROM TABLE

    JOIN

     @Params A

    ON

     A.Param = Table.Column

    I am not sure how IN operator will affect the performance in this case. In general it is slow.

    Regards,
    gova

  • May 31, 2005 at 6:09 pm

    #562058

    Use a function the returns a table to split the values and use the variable in the from clause.

    There are a couple of example scripts in the script library. If you still have problems I can send you the one that we use which is really quick and doesn't use any looping.

     

    --------------------
    Colt 45 - the original point and click interface

  • May 31, 2005 at 6:45 pm

    #562060

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=182965#bm183028

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=179284#bm179295







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 1, 2005 at 1:01 am

    #562085

    http://www.sommarskog.se/arrays-in-sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 1, 2005 at 1:53 am

    #562097

    Hey Frank, do you have a function key setup for that reply yet? It's amazing how many times it gets posted and still no-one searches before posting.

    --------------------
    Colt 45 - the original point and click interface

  • June 1, 2005 at 2:06 am

    #562100

    It's stored as favorite. Just because it is that frequently needed.

    ...no-one searches before posting...

    Sad, but true.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 1, 2005 at 3:01 am

    #562117

    I've been thinking about the features for a perfect discussion forum recently. One of them would be an FAQ that is shown on the Create New Thread page, containing questions and answers such as this one.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • June 1, 2005 at 3:12 am

    #562119

    I don't really think this will help very much.

    But a clean structured FAQ section surely will

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 1, 2005 at 6:10 am

    #562136

    Just to hijack the thread a bit further

    One thing I seen that sort of worked, was to make submitting a new post a three step process.

    1) Enter the details for you post

    2) based on the details entered, list most likely solutions to be relevant

    3) None of the listed solutions helped, continue and submit post.

     

    --------------------
    Colt 45 - the original point and click interface

  • June 1, 2005 at 10:42 am

    #562266

    Thanks for all the advice, I'll just use a sub-select instead of passing in a variable to my IN operator or try the splitting method mentioned (though that may be an overkill for what I'm trying to accomplish). 

    BTW, I did search the site before posting.  I searched for IN operator and also IN operator variables. 

  • June 1, 2005 at 11:24 am

    #562284

    Another, perhaps cheesy bit of ad-hoc-ery works:

    DECLARE  @TheseValues varchar(100)

    SET @TheseValues = 'VAL1,VAL2,VAL3'

    SELECT  <ColumnNames>

    FROM  <TableName>

    WHERE  PatIndex('%' + <ColumnName> + '%',@TheseValues) > 0

    My $0.02. The commas aren't really required in this case, but serve to make it more readable for me.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply