Optionial bit Operator in WHERE clause

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 2008 Forums

Transact-SQL (2008)

Optionial bit Operator in WHERE clause

Author

Topic

metallian1
Starting Member

5 Posts

Posted - 2009-07-30 : 05:30:57

Hello

I have a question concerning bit values in WHERE clauses.

I have a database where each table contains a bit column named IsDisabled. Setting this value to 1 means the record is disabled from regular use.

So normally my stored prodecures go like this

SELECT <columns> FROM <table> WHERE <other Where clauses> AND IsDiabled = 0

So far so good.

From time to time I nonetheless have the need to return all records including the disabled ones regardless of the IsDisabled flag.

So the stored proc would contain something like this

SELECT <columns> FROM <table> WHERE <other Where clauses>

Each of my stored procedures returning data to the client has an input parameter bit @IncludeDisabled. If you set the value to 1, disabled records will be included.

Unfortunately this makes the control logic of the stored procedure quite unhandy because it now goes like this.

IF (@IncludeDisabled = 0)
SELECT <columns> FROM <table> WHERE (<other Where clauses> AND IsDiabled = 0)
ELSE
SELECT <fields> FROM <table> WHERE (<other Where clauses>)

The more complex the SELECT statements become more difficult does it gets to handle the code flow with a lot of if clauses

Long sentence made short. Is there any better way to check for a bit value in WHERE clause when either 0 and 1 records shall be returned so that I can use only one SELECT statement for both checks (one check for IsDisabled = 0 and one for IsDisabled = 0 or 1?

I hope this is somehow understandable.

rgds

Jan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 05:52:29

Don't use separate if clause append with the query itself

select * from table_name where <condition> and IsDiabled = @IncludeDisabled

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

metallian1
Starting Member

5 Posts

Posted - 2009-07-30 : 05:57:34

Thanks

sorry this is not what I intended

If I use this:
select * from table_name where <condition> and IsDiabled = @IncludeDisabled

This would return EITHER the records that are disabled (@IncludeDisabled =1) or that are NOT disabled (@IncludeDisabled=0). What I need is either the enabled ones (@IncludeDisabled=0) or both (in this case I simply want the IsDisabled field not to be checked --> something like WHERE IsDisabled IN (0,1)

rgds

Jan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 06:00:42

quote:
Originally posted by metallian1

Thanks

sorry this is not what I intended

If I use this:
select * from table_name where <condition> and IsDiabled = @IncludeDisabled

This would return EITHER the records that are disabled (@IncludeDisabled =1) or that are NOT disabled (@IncludeDisabled=0). What I need is either the enabled ones (@IncludeDisabled=0) or both (in this case I simply want the IsDisabled field not to be checked --> something like WHERE IsDisabled IN (0,1)

rgds

Jan

Sorry I can't understand your requirement!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-30 : 08:42:50

SELECT <columns> FROM <table> WHERE (<other Where clauses> AND
((IsDiabled =@IncludeDisabled and @IncludeDisabled=0) or (IsDiabled in(0,1) and @IncludeDisabled=1 ))

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-30 : 13:14:10

No need to check the value of IsDisabled if you want all.

quote:
Originally posted by madhivanan

SELECT <columns> FROM <table> WHERE (<other Where clauses> AND
((IsDiabled =@IncludeDisabled and @IncludeDisabled=0) or (~~IsDiabled in(0,1) and~~ @IncludeDisabled=1 ))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-31 : 02:22:50

quote:
Originally posted by Lamprey

No need to check the value of IsDisabled if you want all.
quote:
Originally posted by madhivanan

SELECT <columns> FROM <table> WHERE (<other Where clauses> AND
((IsDiabled =@IncludeDisabled and @IncludeDisabled=0) or (~~IsDiabled in(0,1) and~~ @IncludeDisabled=1 ))

Yes. It is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources