Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Delimited List Of Items As input parameter

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,
0
tinman1412
Asked:
tinman1412
2 Solutions
 
LowfatspreadCommented:
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
0
 
Brian CroweDatabase AdministratorCommented:
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
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now