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.

Multiple Update with multiple condition

I'm having an Employee table with a Salary field. How can we increate the
salary of the employees with following conditions:
1) salary between 1000 and 10000 : increase 25%
2) salary between 10000 and 20000 : increase 15%
3) salary between 20000 and 30000 : increase 5%

Surely you can create a cursor to solve this. But the question is, Is it
possible to solve this in a single query, if no what is most optimized
way?Try:

UPDATE Employee
SET salary = salary *
CASE
WHEN salary>=1000 AND salary<10000 THEN 1.25
WHEN salary>=10000 AND salary<20000 THEN 1.15
WHEN salary>=20000 AND salary<30000 THEN 1.5
END
WHERE salary>=1000 AND salary<30000

--
David Portas
SQL Server MVP
--|||Thanks friend|||Thanks friend|||Thank you friend

Multiple table join

First thank you very much for all your helps, and pls.

Ok, this is what I am trying to do:,
1) I am trying to get the number of employees that has completed all their
online training within 10 days of hire
2) All the employees that are has no exception(no pre-service training) and
has completed their checklist within 10 days
3) all the exception(pre-service) employees that has completed their
training within 70 days.

I have these tables, hipaa2006, hipaa101, hipaa201_inputs,
domesticviolence, securityawareness, securityawareness2006,
civilrights_input, and people_first_data.
People_first_data contains all the employees we have in our database, it
has empl_pfid col, but does not have last_modified col which all the other
tables has.

I have this queries:

1) select distinct(count(empl_pfid))
from people_first_data
left outer join tbl_domesticviolence on people_first_data.empl_pfid =
tbl_domesticviolence.employee_pfid
left outer join tbl_hipaa101 on
people_first_data.empl_pfid=tbl_hipaa101.employee_ pfid
left outer join tbl_hipaa201_input on people_first_data.empl_pfid=
tbl_hipaa201_input.employee_pfid
left outer join tbl_hipaa2006 on
people_first_data.empl_pfid=tbl_hipaa2006.employee _pfid
left outer join tbl_securityawareness on
people_first_data.empl_pfid=tbl_securityawareness. employee_pfid
left outer join tbl_securityawareness2006 on people_first_data.empl_pfid=
tbl_securityawareness2006.employee_pfid
where people_first_data.empl_pfid = '639846'
and tbl_domesticviolence.last_modified is not null
and tbl_hipaa101.last_modified is not null
and tbl_hipaa201_input.last_modified is not null
and tbl_hipaa2006.last_modified is not null
and tbl_securityawareness.last_modified is not null
and tbl_securityawareness2006.last_modified is not null

2) ( I tried union, but i only wanted one number)
select count(last_modified) as date1 from tbl_hipaa2006
union
select count(last_modified) as date2 from tbl_hipaa101

every help will be highly appreciated.Hi there,

Kindly refer to below attached link.