CASE function within WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE function within WHERE

Author

Topic

coke_always
Starting Member

1 Post

Posted - 2010-05-02 : 17:58:24

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

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-02 : 19:17:49

If I understand what you need then you can try one of these:


SELECT *
  FROM EMPLOYEES
 WHERE @empid IS NULL
    OR LEN(@empid) = 0

 UNION ALL

SELECT *
  FROM EMPLOYEES
 WHERE @empid IS NOT NULL
   AND LEN(@empid) > 0
   AND EXISTS(SELECT *
                FROM @tblEmpid
               WHERE iEmpid = empid)


IF @empid IS NULL OR LEN(@empid) = 0
   SELECT *
     FROM EMPLOYEES
ELSE
   SELECT *
     FROM EMPLOYEES
    WHERE EXISTS(SELECT *
                   FROM @tblEmpid
                  WHERE iEmpid = empid)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 01:35:39

[code]SELECT e.*
FROM EMPLOYEES e
LEFT JOIN @tblEmpid t
ON t.empid = e.empid
WHERE t.empid IS NOT NULL
OR LEN(@empid) = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources