Select help - 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)

Select help

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2005-04-26 : 11:04:51

I want to select records based on the value of a column. I have three conditions and the column is boolean. The user selects True (1), False(0), both true and false(3).

I am not sure how to get this done. Can I use a case in the WHERE clause? Will I have to write three seperate selects? How can I select both true and false?

Thanks,
vmon

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-26 : 11:15:01

Here's one way:


declare @condition tinyint

set @condition = 0
set @condition = 1
set @condition = 3


Select *
From	(
	Select convert(bit, 0) bitcol union all 
	select 1 union all 
	select 0 union all 
	select 1
	) a
where	@condition = 
		case @condition
		when 3 then @condition
		else bitcol
		end

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-26 : 11:58:56

another way:


declare @selection tinyint
select 	@selection = 3
select 	* 
from 	MyTable
Where	(YourBitColumn = 1 and @selection = 1)	-- select all true's
	or (YourBitColumn = 0 and @selection = 0) -- select all false's
	or (@selection = 3)			-- select all

Go with the flow & have fun! Else fight the flow

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-27 : 03:05:56

SELECT * FROM x_table
WHERE x_condition =
CASE
WHEN @condition = 1--(true)
THEN 1
WHEN @condition = 0--(false)
THEN 0
ELSE x_condition --(both)
END

Brought to you by:
BlackShoe Productions
Mike Petanovitch

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-27 : 14:53:31

Depending on the input params, it may be safer to use another Case for (3) rather than an ELSE. Are you using NULL as the BIT's value (3)? Or is the input a char param, like below:

DECLARE @myTable TABLE (myName varchar(10), myBit BIT)
INSERT INTO @myTable SELECT 'True',1 UNION SELECT 'False',0

DECLARE @input CHAR(1)

SET @input = 3

SELECT myName, myBit 
FROM @myTable  
WHERE myBit = 
		CASE @input
				WHEN 1 THEN 1
				WHEN 0 THEN 0
				WHEN 3 THEN myBit
		END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-27 : 15:17:30

So what now...wait for Paula, Randy, and Simon to declare a winner?

Be One with the Optimizer
TG

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-27 : 15:31:09

yup

jhermiz

3564 Posts

Posted - 2005-04-27 : 15:53:26

[]Daula is too busy smiling about god knows what. Randy's a "dawg"..and Simon well he's not worth mentioning :)

On that note go Carrie!

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources