asked on

Passing string data into a function for use in IN clause

Hello,

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.

TTom

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".

Tom

ispaleny

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

ispaleny

GO
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
GO

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

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ispaleny

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

Good luck!

ahn831

ASKER

Thank you! Thank you! Thank you!

Yes, the second solution was better. IOU all.

routledge

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+')')

ispaleny

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