Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Red face Unanswered: NOT IN a list of string

    Hi,

    i want to compare a column data with a list of string, and this string actually already predefined in a variable.

    for example

    set @skipcolDef = @skipcol + '''TEST'',''TEST1'''

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SAMPLE' AND COLUMN_NAME NOT IN (@skipcolDef)

    where @skipcol is a variable pass in when execute stored procedure.

    example above when execute through query analyser it return execute successfully but with wrong result.

    Actually how should i do for this case?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I think it's because the @skipcolDef is considered as a single column instead of a list of columns.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The quick and easy way to solve this is to put the values of interest into a temp table (one row per value of interest), then use a NOT EXISTS instead of your NOT IN. It has the side benefit of being much more efficent too!

    -PatP

  4. #4
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by Pat Phelan
    The quick and easy way to solve this is to put the values of interest into a temp table (one row per value of interest), then use a NOT EXISTS instead of your NOT IN. It has the side benefit of being much more efficent too!

    -PatP
    Thank you. But i just wanna why we can use in this way?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use something like:
    Code:
    CREATE TABLE #skip (
       colName sysname NOT NULL)
    
    INSERT #skip (colName)
       SELECT 'Test'
       UNION ALL 'Test1'
    
    SELECT COLUMN_NAME
       FROM INFORMATION_SCHEMA.COLUMNS AS c
       WHERE TABLE_NAME = 'SAMPLE'
          AND NOT EXISTS (SELECT *
             FROM #skip AS s
             WHERE s.colName = c.COLUMN_NAME)
    -PatP

  6. #6
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by Pat Phelan
    Use something like:
    Code:
    CREATE TABLE #skip (
       colName sysname NOT NULL)
    
    INSERT #skip (colName)
       SELECT 'Test'
       UNION ALL 'Test1'
    
    SELECT COLUMN_NAME
       FROM INFORMATION_SCHEMA.COLUMNS AS c
       WHERE TABLE_NAME = 'SAMPLE'
          AND NOT EXISTS (SELECT *
             FROM #skip AS s
             WHERE s.colName = c.COLUMN_NAME)
    -PatP

    I test by using table variable, when execute, it prompt incorrect syntax near 'Test1'

  7. #7
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by GongXi
    I test by using table variable, when execute, it prompt incorrect syntax near 'Test1'

    Opps, now i know the problem, select is missing infront of 'Test1'. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •