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

How to add an "if then else" condition within an expression?

Greetings:

I am creating a complex expression in a Send Mail Task for the MessageSource. The expression contains the values of many package variables. I want to conditionally include some variables in the expression depending on what value they contain.

Is it possible to create conditional logic (if then else) within an expression? I've got to conditionally include 8 different variables.

Thanks,

BCB

Using the expression editor, you can look at the Operators. One of them is the "conditional."

This is what you want. (?

your test ? if true : else false|||

Thanks, Phil.

This is part of the code I came up with to conditionally include "lines" in the body of my email message. The 2nd and 4th lines will appear only if the value of a variable is not empty. (Those are the lines that begin with Trim.) The key thing I discovered is that you need to enclose the conditional logic in parens in order for the overall expression to evaluate properly.

"The number of ESHL rows inserted was " + (DT_STR, 10, 1252) @.[User::ESHL_RowsInserted] + "." + "\n" +
(Trim(@.[User::ESHL_Error_LongMessage]) == "" ? "" : "\n" + "ESHL Message: " + @.[User::ESHL_Error_LongMessage] + "\n\n") +
"The number of Gulf States rows inserted was " + (DT_STR, 10, 1252) @.[User::GulfStates_RowsInserted] + "." + "\n" +
(Trim(@.[User::GulfStates_Error_LongMessage]) == "" ? "" : "\n" + "Gulf States Message: " + @.[User::GulfStates_Error_LongMessage] + "\n\n")