Passing string data into a function for use in IN clause

Posted on 2003-03-09
Medium Priority
Last Modified: 2007-12-19

I'm working on a calendar component that has different categoryies for the events on the calendar. I need to be able to select all, or some of the categories when displaying the calendar. I have a function written that should do this:

GetCalData(DispDate datetime, CalendarID int, CategoryIDs varchar(100))

and what I'd like to do is put something like 4,12,16,20,25 in the CategoryIDs variable and enclose that in an IN, similar to:

SELECT * FROM Events WHERE (CalendarID = @CalendarID) AND (EventDate BETWEEN @DispDate AND DateAdd(m,1,@DispDate)) AND (CategoryID IN(4,12,16,20,25))
(I don't have the exact code in front of me so please forgive any syntactical errors [ie, with DateAdd])

I can't seem to get this to work because the CategoryID is an interger field in the database. Does anyone have any ideas as to how I'd do this? I'm trying to avoid limiting the number of Category's allowed to be displayed, and think I've tried everything.

Thank you very much.
Question by:ahn831
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8098019
Code looks OK.  What is happening when you try to run it?  Are you getting errors?  If not, try breaking up the query into smaller pieces to be sure each one is giving you the sort of data you expect, e.g.,

SELECT * from CalendarEvents where CalendarID = @CalendarID;
SELECT * from CalendarEvents where EventDate BETWEEN @DispDate AND DateAdd(m,1,@DispDate);
SELECT * from CalendarEvents where CategoryID in (4,12,16,20,25);

That might help you to isolate where the query is "breaking down".

LVL 13

Expert Comment

ID: 8098055
select '15 is in set' where charindex('15',',1,2,10,13,15,20,50')>0
LVL 13

Expert Comment

ID: 8098111
create function dbo.GetCalData(@DispDate datetime, @CalendarID int, @CategoryIDs varchar(100))
returns table with schemabinding as return
SELECT CalendarID,EventDate,CategoryID
FROM dbo.Events
WHERE (CalendarID = @CalendarID)
AND (EventDate BETWEEN @DispDate AND DateAdd(m,1,@DispDate))
AND charindex(','+convert(varchar(10),CategoryID)+',',@CategoryIDs)>0

select * from dbo.GetCalData('2003-03-09',1,',1,2,4,6,10,15,20,30,')
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Accepted Solution

ispaleny earned 300 total points
ID: 8098132
create function dbo.ExpandNums(@CategoryIDs varchar(100))
returns @t table (CategoryID int primary key clustered) with schemabinding as
 declare @OldPos int
 declare @Pos int
 declare @Len int
 set @Len=len(@CategoryIDs)
 set @OldPos=1
 set @Pos=CHARINDEX(',',@CategoryIDs,@OldPos+1)
 while @Pos>0 begin
  insert @t(CategoryID) values (SUBSTRING(@CategoryIDs,@OldPos+1,@Pos-@OldPos-1))
  set @OldPos=@Pos
  set @Pos=CHARINDEX(',',@CategoryIDs,@OldPos+1)
create function dbo.GetCalData2(@DispDate datetime, @CalendarID int, @CategoryIDs varchar(100))
returns table with schemabinding as return
SELECT CalendarID,EventDate,dbo.Events.CategoryID
FROM dbo.Events
JOIN dbo.ExpandNums(@CategoryIDs) x on dbo.Events.CategoryID=x.CategoryID
WHERE (CalendarID = @CalendarID)
AND (EventDate BETWEEN @DispDate AND DateAdd(m,1,@DispDate))

select * from dbo.GetCalData2('2003-03-09',1,',1,2,4,6,10,15,20,30,')
LVL 13

Expert Comment

ID: 8098141
The second solution is faster (indexed) for large tables.

Good luck!

Author Comment

ID: 8098673
Thank you! Thank you! Thank you!

Yes, the second solution was better. IOU all.

Expert Comment

ID: 8098756
Why not build an SQL string and execute that?...

This snippet haas the same effect if you KNOW the format of the input string with its list of IDs:

Declare @strList varchar(20)
Drop Table #tblTest
create table #tblTest (
id int,
name varchar(10))

INSERT into #tblTest values (1,'A')
INSERT into #tblTest values (2,'B')
INSERT into #tblTest values (3,'C')
INSERT into #tblTest values (4,'D')
INSERT into #tblTest values (5,'R')
INSERT into #tblTest values (6,'F')
INSERT into #tblTest values (7,'G')
--Example of the type of SQL query hard coded
Select * from #tblTest where id IN (1,2,3)
-- set up @strList as if it was a list coming into the routine
Set @strList = '1,2,3'
-- Create and run the required SQL with the IN statement using the list
EXEC ('Select * from #tblTest where id IN ('+ @strList+')')

LVL 13

Expert Comment

ID: 8098803
If you have #table built, you do not need to dyna-exec.
Only add an index and join tables.

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

764 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