How to add condition to where clause?

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,
--
bicbic wrote:
> I have a long query and therefore want to avoid using it twice in an if el
se
> 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,
One way is to add:
AND (id = @.id OR @.id IS NULL)|||WHERE (@.id IS NULL OR theId<>555)
On Thu, 22 Jun 2006 11:10:02 -0700, bic
<bic@.discussions.microsoft.com> wrote:

>I have a long query and therefore want to avoid using it twice in an if els
e
>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,|||If I understood it right.
one way
(id = @.id or @.id is null)
another way
id = coalesce(@.id,id)
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If you posted the entire query, it would be easier to understand what you ar
e trying to accomplish.
Pehaps this idea could work for you. (It would require @.ID = 0 rather than n
ull)
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
"bic" <bic@.discussions.microsoft.com> wrote in message news:12381DCD-549E-40D0-B85B-EC9A87A
A005C@.microsoft.com...
>I have a long query and therefore want to avoid using it twice in an if els
e
> 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,
> --
> bic|||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.
bic
"bic" wrote:

> I have a long query and therefore want to avoid using it twice in an if el
se
> 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,
> --
> bic|||bic wrote:
> Perhaps I did not make myself clear; I am trying to implement the followi
ng,
> AND theid= CASE WHEN @.id=NULL THEN <>55 ELSE @.id END
> can someone offer help to fix my syntax. Thanks.
>
AND ((@.id IS NULL AND theid <> 55) OR (theid = @.id))|||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
)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23N1ZptilGHA.1276@.TK
2MSFTNGP03.phx.gbl...
If you posted the entire query, it would be easier to understand what you ar
e trying to accomplish.
Pehaps this idea could work for you. (It would require @.ID = 0 rather than n
ull)
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
"bic" <bic@.discussions.microsoft.com> wrote in message news:12381DCD-549E-40D0-B85B-EC9A87A
A005C@.microsoft.com...
>I have a long query and therefore want to avoid using it twice in an if els
e
> 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,
> --
> bic