Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unhappy Unanswered: CASE function within WHERE

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    IF LEN(LTRIM(RTRIM(@empid))) > 0
      SELECT * FROM EMPLOYEES
       WHERE empid IN (select iEmpid from @tblEmpid))
    ELSE
      SELECT * FROM EMPLOYEES
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2010
    Posts
    3

    CASE function within WHERE

    thanks r937 for the reply. actually i wasnt looking for a dynamic sql as the reply, as the select statement contains much more than the employee_id thing. thus i was looking for a solution using the CASE function.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude

    i solved the problem you posted

    if you didn't post the problem you wanted solved, that's too bad, innit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2010
    Posts
    3

    Post CASE function within WHERE

    sorry r937, i didnt understand your reply. if u managed to resolve the issue, could u please send me the solution.


    thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy's solution does not use dynamic SQL and it perfectly solves the problem you posted. If, as you say, your problem is not what you posted then you need to post the real problem.

    Also, do not get hung up on using CASE. The best solutions often do not use CASE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •