CASE statement in WHERE clause problem

Hello,
I would like to achieve the following within an stored procedure.
SELECT * FROM TableX WHERE ID = 1
OR
SELECT * FROM TableX WHERE ID IS NOT NULL
How can I solve this by using a condition in my WHERE clause?
eg.
//////
CREATE PROCEDURE TestID
@.ID INT
AS
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE NOT NULL
END
////
The problem is the ' NOT ' NULL in the ELSE Path
If I skip the ELSE Path then it will be implicitly NULL
Thanks for any help,
RemcoHi,
Try this
SELECT * FROM TableX WHERE ID = 1 OR ID IS NOT NULL
Hth
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Hello Remco,

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
if you want criteria:
1. @.ID != null --> ID = @.ID
2. @.ID IS NULL --> ID IS NOT NULL
then:
WHERE
(@.ID IS NOT NULL AND ID = @.ID)
OR (@.ID IS NULL AND ID IS NOT NULL)
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B|||this should get you started on how to do that.
http://www.aspfaq.com/show.asp?id=2501
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||Try,
SELECT * FROM TableX
WHERE [ID] = @.id or (@.id is null and [id] is null)
AMB
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||
SET ANSI_NULLS ON
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE ID
END
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Reading your post again, I realized that what you want is:
select * from tablex
where ([id] = @.id) or (@.id is null and [id] is not null)
AMB
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM TableX
> WHERE [ID] = @.id or (@.id is null and [id] is null)
>
> AMB
> "Remco" wrote:
>|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...

> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
Possibly (untested):
SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in message
news:1fdb1$420b726d$432498ca$16254@.allth
enewsgroups.com...
> SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
I think that will fail if ID is null. Better is
SELECT * FROM TableX where ISNULL(ID, '') = ISNULL(@.ID,'')|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
SELECT * FROM TableX WHERE ID IS NOT NULL
satisfies this condition, but I'm gathering that's not what you want. :)