We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Delimited List Of Items As input parameter

tinman1412
tinman1412 asked
on
Medium Priority
260 Views
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,
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
after you've inserted the passed parms into a temp table

use the EXISTS/NOT Exists clause

e.g.

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


or if you're using dynamic sql then


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

exec(@SQL)

hth

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
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)
)
BEGIN
DECLARE @Item varchar(100)
DECLARE @Index int

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

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

CREATE PROCEDURE myProc
   @parm varchar(100)

SELECT ...
FROM myTable
LEFT OUTER JOIN dbo.ParseList(@parm, ';') AS ListItems
   ON myTable.ID = ListItems.ListItem
WHERE myTable.ID IS NULL
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.