WHERE Column is @var? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

WHERE Column is @var?

Author

Topic

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-24 : 16:07:45

Ok here is where I am:

DECLARE @var as varchar(10)
IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT NULL )
BEGIN
SET @expnull = ' IS NOT NULL'
ELSE
SET @expnull = ' IS NULL'
select * from Typle where (Nom @var)

How can I use a variable set conditionnaly in the where clause to set if it null or not

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 16:32:30

Very strange logic but will this work for you? (you can avoid dynamic sql this way)


DECLARE @var as varchar(10)
IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT NULL )
BEGIN
	SET @expnull = ' IS NOT NULL' 
ELSE 
	SET @expnull = ' IS NULL'

--select * from Typle where (Nom @var)

select [<use explicit list of columns>] 
from	Typle 
where	isnull(Nom,' IS NULL') = 
		case
		when @expnull = ' IS NULL' then @expnull
		else Nom
		end

Be One with the Optimizer
TG

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-24 : 16:45:11

God thanx!
I thought it would be very complicated.
I guess your good!

I will have a try.

Thank you again

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-24 : 16:48:17

Why not code the SELECT twice. Nutin wrong with that IMHO.

IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT NULL )
BEGIN
  SELECT * FROM Typle WHERE Nom IS NOT NULL
END ELSE BEGIN
  SELECT * FROM Typle WHERE Nom IS NULL
END

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-24 : 16:53:55

If the method said by TG failed or if it seems better to me , i'll have a try!

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-24 : 16:58:36

Just a side question:

Can I

WHERE (CASE
IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT
NULL)
THEN Nom IS NOT NULL
ELSE Nom IS NULL
END)

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-24 : 22:28:07

quote:
Originally posted by scrap

Can I

WHERE (CASE
IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT
NULL)
THEN Nom IS NOT NULL
ELSE Nom IS NULL
END)

No. CASE returns a value. It can be used like this:

WHERE Expression = CASE .... THEN ... ELSE ... END

TG had it right, but I find the solution I posted is easier for me to read and understand.

If you really don't want two SELECTs, there is another way that TG's query condition could be written...


IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT NULL)
    @MyBoolean = 1
  ELSE
    @MyBoolean = 0

SELECT *
  FROM Typle
  WHERE (@MyBoolean = 1 AND Nom IS NOT NULL)
    OR  (@MyBoolean = 0 AND Nom IS NULL)

or, throw in a CASE to "simplify" the condition.  (Whether it's simpler is a matter of opinion.)

SELECT *
  FROM Typle
  WHERE @MyBoolean = CASE WHEN Nom IS NULL THEN 0 ELSE 1 END

Although this condition is shorter, I have to think harder to understand what it's doing.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-25 : 08:16:18

Thank you
I do some tests and come back.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-25 : 10:59:14

Finally it is kinda working well.

I use a view that returns 3 columns.
The third column sometimes contains some NULL datas.
I want when non-NULL datas exist in the third column, to only keep non-NULL datas. And if only NULL datas exist in the third column to only keep NULL datas.

____________________________________________________________________

IF EXISTS (SELECT Column3 FROM View WHERE Column3 IS NOT NULL GROUP BY Column3)
BEGIN
SELECT * FROM View WHERE Column3 IS NOT NULL
ELSE
SELECT * FROM View WHERE Column3 IS NULL
END
____________________________________________________________________

Sorry I didn't explain the problem before!

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-25 : 11:40:35

quote:
Originally posted by scrap

The third column sometimes contains some NULL datas.
I want when non-NULL datas exist in the third column, to only keep non-NULL datas. And if only NULL datas exist in the third column to only keep NULL datas.

I'd like to help, but I really don't understand what you're trying to do.

I'm not sure what you mean by "keep"? "Keep NULL datas and "keep" non-NULL datas" what does that mean? Sounds like you want to select the data into a rowset if Column3 is NULL or NOT NULL which means any value of Column3.

Looking at your query, the GROUP BY tells me that you're not doing what you had hoped to do.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-25 : 13:26:17

-Here is what the view return:

ex.
John 123 NULL
Peter 456 098
Joe 789 765

-Here is what I want to select from the view:

If non-NULL datas exist in the third column, select non-NULL datas.
Else select *.

Hope it can help you understanding my problem.
ThanK you very much!

Kristen
Test

22859 Posts

Posted - 2005-08-25 : 13:35:59

Guys am I being picky here, or what?

I'm not happy with this code "style", but probably 'coz I've never tried it before:


IF EXISTS (SELECT Column3 FROM View WHERE Column3 IS NOT NULL GROUP BY Column3)
BEGIN
SELECT * FROM View WHERE Column3 IS NOT NULL
ELSE
SELECT * FROM View WHERE Column3 IS NULL
END

my style would be


IF EXISTS (SELECT Column3 FROM View WHERE Column3 IS NOT NULL GROUP BY Column3)
BEGIN
SELECT * FROM View WHERE Column3 IS NOT NULL
END
ELSE
BEGIN
SELECT * FROM View WHERE Column3 IS NULL
END

(Of course you can leave the Begin/End out if you only have one statement, but that's a classic future-failure technique

, so I always include them.)

Kristen

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-25 : 14:43:32

Thank you Kirsten.

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-26 : 12:17:04

quote:
Originally posted by scrap

-Here is what the view return:

ex.
John 123 NULL
Peter 456 098
Joe 789 765

-Here is what I want to select from the view:

If non-NULL datas exist in the third column, select non-NULL datas.
Else select *.

Well, I continue to remain confused... Looks like there are 3 columns...

quote:
If non-NULL datas exist in the third column, select non-NULL datas.

That would be

SELECT Col1, Col2, Col3 FROM MyTable WHERE Col3 IS NOT NULL

quote:
Else select *.

This doesn't look much different
SELECT * FROM MyTable WHERE Col3 IS NULL
-- Since there are 3 columns, SELECT * is the same as --
SELECT Col1, Col2, Col3 FROM MyTable WHERE Col3 IS NULL

I don't see any difference in the requirements of the return recordset.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-08-26 : 16:48:41

quote:

Originally posted by TG

Very strange logic but will this work for you? (you can avoid dynamic sql this way)


DECLARE @var as varchar(10)
IF EXISTS (SELECT Sde_NRI FROM Pal WHERE Pal.Sde_NRI IS NOT NULL )
BEGIN
	SET @expnull = ' IS NOT NULL' 
ELSE 
	SET @expnull = ' IS NULL'

--select * from Typle where (Nom @var)

select [<use explicit list of columns>] 
from	Typle 
where	isnull(Nom,' IS NULL') = 
		case
		when @expnull = ' IS NULL' then @expnull
		else Nom
		end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources