I have a long query and therefore want to avoid using it twice in an if else
structure, while still be able to achieve excluding an id when
@id=null(meaning all included) in the where clause or like.
i.e.
if @id=null
theId<>555

Thanks,

WHERE (@id IS NULL OR theId<>555)


On Thu, 22 Jun 2006 11:10:02 -0700, bic
<b**@discussions.microsoft.com> wrote:

Show quoteHide quote

bic wrote:
One way is to add:

AND (id = @id OR @id IS NULL)

If I understood it right.
one way

(id = @id or @id is null)

another way

id = coalesce(@id,id)

Hope this helps.

If you posted the entire query, it would be easier to understand what you are trying to accomplish.

Pehaps this idea could work for you. (It would require @ID = 0 rather than null)

USING NORTHWIND
GO

DECLARE @Test int

SET @Test = 0

SELECT
     EmployeeID
   , LastName
FROM   Employees
WHERE (  @Test = CASE @Test WHEN 0 THEN @Test ELSE -1 END
      OR EmployeeID = @Test
      )

SET @Test = 5

SELECT
     EmployeeID
   , LastName
FROM   Employees
WHERE (  @Test = CASE @Test WHEN 0 THEN @Test ELSE -1 END
      OR EmployeeID = @Test
      )

Show quoteHide quote

For your particular situation:

(If you can pass in a 0 instead of a NULL for @ID to get all records -except 555)

WHERE (  (   @ID = CASE @ID WHEN 0 THEN @ID ELSE -1 END
         AND theID <> 555
         )
      OR EmployeeID = @ID
      )


  If you posted the entire query, it would be easier to understand what you are trying to accomplish.

  Pehaps this idea could work for you. (It would require @ID = 0 rather than null)

  USING NORTHWIND
  GO

  DECLARE @Test int

  SET @Test = 0

  SELECT
       EmployeeID
     , LastName
  FROM   Employees
  WHERE (  @Test = CASE @Test WHEN 0 THEN @Test ELSE -1 END
        OR EmployeeID = @Test
        )

  SET @Test = 5

  SELECT
       EmployeeID
     , LastName
  FROM   Employees
  WHERE (  @Test = CASE @Test WHEN 0 THEN @Test ELSE -1 END
        OR EmployeeID = @Test
        )

  --
  Arnie Rowland, YACE*
  "To be successful, your heart must accompany your knowledge."

  *Yet Another Certification Exam


Show quoteHide quote

Perhaps I did not make myself clear;  I am trying to implement the following,

    AND   theid= CASE WHEN @id=NULL THEN <>55 ELSE @id    END

can someone offer help to fix my syntax.  Thanks.

Show quoteHide quote

bic wrote:
AND ((@id IS NULL AND theid <> 55) OR (theid = @id))

ORDER BY DATETIME field BUG?
Subquery performance concern
A query runs 1 times slower from a .NET application the from Query Analizer
Help with varchar to date
schedule dts at a remote server