Delimited List Of Items As input parameter

Posted on 2006-04-03
Last Modified: 2012-06-27
Hi All,

I'm sending a delimited string value to my stored proc as an input parameter. It looks like this ('1;2;3;4;5').
I have function that will I call inside a while loop that will extract each value. I'm stuck with the following problem.
I want to write a query that will select items from a table that will not include values from my delimted list. For example,
I have a table with 10 records with unique id's from 1 to 10. If I use my delimited list from above, I want to only retrieve records
from id's 6-10 and exclude 1- 5.  How do I go about doing this in my proc.  Thanks,
Question by:tinman1412
    LVL 50

    Accepted Solution

    after you've inserted the passed parms into a temp table

    use the EXISTS/NOT Exists clause


    Select a.* from mytable as a
    where not exists (select from #temp where =

    or if you're using dynamic sql then

    set @sql = 'select a.* from mytable as a where not in (''' + Replace(@parm,';','''')+''')'


    LVL 34

    Assisted Solution

    by:Brian Crowe
    Here is a function that will parse the list into a table for you

    CREATE FUNCTION ParseList(@List varchar(8000), @Delim char(1))
    RETURNS @ListItems TABLE (
          Rank int IDENTITY(1,1),
          ListItem varchar(100)
    DECLARE @Item varchar(100)
    DECLARE @Index int

    WHILE LEN(@List) > 0
          SET @Index = CHARINDEX(@Delim, @List)
          IF @Index = 0
                SET @Item = RTRIM(LTRIM(@List))
                SET @List = ''
                SET @Item = RTRIM(LTRIM(SUBSTRING(@List, 1, @Index - 1)))
                SET @List = SUBSTRING(@List, @Index + 1, LEN(@List))
          INSERT INTO @ListItems (ListItem) VALUES (@Item)

    After you add this function to your database use it like such...

       @parm varchar(100)

    SELECT ...
    FROM myTable
    LEFT OUTER JOIN dbo.ParseList(@parm, ';') AS ListItems
       ON myTable.ID = ListItems.ListItem
    WHERE myTable.ID IS NULL

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now