LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » CASE function within WHERE

CASE function within WHERE

Options

Previous Topic · Next Topic

coke_always

Posted: Sunday, May 02, 2010 5:08:33 PM

Rank: Newbie

Joined: 5/2/2010
Posts: 1
Points: 3
Where do you live?: USA

I have this issue in T-Sql -

a stored proc - GetEmpoyees, which takes in multiple employee_id's as a string, delimited by

"," - a comma. Within the stored-proc an in inbuilt function - fn_split() is called for,

splits the string and inserts them in a temp table @tblEmpID.
the select statement that follows should take care of both the cases
i.) if the empoyee_id string is null
ii.)if employee_id string is not null

SELECT *
FROM EMPLOYEES
WHERE
(CASE
WHEN ((@empid IS NOT NULL AND LEN(LTRIM(RTRIM(@empid)))>0)

THEN
empid IN (select iEmpid from @tblEmpid))
ELSE
empid = empid
END)

OR

SELECT *
FROM EMPLOYEES
WHERE empid in
(CASE
WHEN ((@empid IS NOT NULL AND LEN(LTRIM(RTRIM(@empid)))>0)

THEN
(select iEmpid from @tblEmpid))
ELSE
select empid
END)

i tried both the select statements above and they are throwing an error.

Thanks a lot for the help

Scott Whigham

Posted: Monday, May 03, 2010 8:45:19 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

It would help if you could tell me the errors that you are receiving. Also, are you getting the same error for both or different errors?

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.