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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Passing string data into a function for use in IN clause


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

select '15 is in set' where charindex('15',',1,2,10,13,15,20,50')>0
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,')
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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,')
The second solution is faster (indexed) for large tables.

Good luck!
ahn831Author Commented:
Thank you! Thank you! Thank you!

Yes, the second solution was better. IOU all.
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+')')

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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