Multiple Values to single parameter

Hi,
I have a situation where in I got to pass more than one value to a single parameter like I want to see all the employees of department 5,6 & 7 & so on, my query goes like this "Select firstname,lastname, title from employees where departmentid=@.departmentid". In this variable @.deparmentid I need to pass more than one value. is it possible? if it can, dude's pull it fast to me.

Hi adonis

It is possible I use SQL to check for a comma seperated list. Create this function and pass it a comma seperated list, it will return a table with the values. Change your SQL to something like this:
"Select firstname,lastname, title from employees where departmentid IN ( SELECT [ENTRY] FROM ListToTable(@.departmentid))". It should return the desired result. However you should add maybe an IF to check if the user want all employees and not just a few

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ListToTable') DROP FUNCTION ListToTable GO

CREATE FUNCTION ListToTable
(
/*
** Usage: select entry from listtotable('abc,def,ghi') order by entry desc
*/
@.mylist varchar(8000) )
RETURNS @.ListTable TABLE
(
seqid int not null,
entry varchar(255) not null
)

AS

BEGIN
DECLARE @.this varchar(255),
@.rest varchar(8000),
@.pos int,
@.seqid int

SET @.this = ' ' SET @.seqid = 1 SET @.rest = @.mylist SET @.pos = PATINDEX('%,%', @.rest) WHILE (@.pos > 0) BEGIN set @.this=substring(@.rest,1,@.pos-1) set @.rest=substring(@.rest,@.pos+1,len(@.rest)-@.pos) INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this) SET @.pos= PATINDEX('%,%', @.rest) SET @.seqid=@.seqid+1 END set @.this=@.rest INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this)
RETURN
END
, Hope this helps
l0n3i200n

|||

I really appriciate your effort. but dont you think it all a work around we are fiddling the query here, Insted I was looking for any option in the reporting services thru wich we can send multiple values. Your solution is feasible when you have a small query but you Iam playing with arount more that 25 tables the queries are so complex that if I fiddle those than Its gona suck me.

Any ways thanks and if U have any thing coming up please update me.