May 2, 2010 at 4:01 pm
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
May 2, 2010 at 11:30 pm
Hi,
Try this:
SELECT *
FROM #EMPLOYEES
WHERE
empid in (
select
CASE
WHEN (@empid IS NOT NULL AND LEN(LTRIM(RTRIM(@empid)))>0)
THEN iEmpid end
from @tblEmpid )
--ELSE
--empid = empid
Also Im sorry to tell you that I didnt understand what you are trying do in the ELSE part, if it not required remove it, I just commented it.
Thanks & Regards,
MC
May 3, 2010 at 12:29 am
SELECT *
FROM EMPLOYEES
WHERE @empid IS NULL OR empid IN (SELECT iEmpid FROM @tblEmpid)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply