t sql with a bit who can be null

Question

Post reply

t sql with a bit who can be null

christophe.bernard 47659

Ten Centuries

Points: 1169
More actions
July 4, 2012 at 5:56 am

#271976

Hi,
I work with sql server 2005 and i have a simple table with some columns
declare @tbl table (id int, nom varchar(20) ,actif bit )
INSERT @tbl values(1, 'un', 1)
INSERT @tbl values(2, 'deux', 0)
INSERT @tbl values(3, 'trois', 1)
INSERT @tbl values(3, 'quatre', 1)
INSERT @tbl values(3, 'cinq', 1)
I'd like to have this :
if parameters @actif is null I MUST TO SELECT ALL VALUE otherwise i must to select follow the flag actif !
I'm a little lost with the condition ?!
declare @actif bit = null
SELECT * FROM @tbl where actif = @actif
any idea ?
Thanks for your time
christophe

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113498 More actions · Answer 1

this will get you your requirements

declare @tbl table (id int, nom varchar(20) ,actif bit )

INSERT @tbl values(1, 'un', 1)

INSERT @tbl values(2, 'deux', 0)

INSERT @tbl values(3, 'trois', 1)

INSERT @tbl values(3, 'quatre', 1)

INSERT @tbl values(3, 'cinq', 1)

declare @actif bit = null

if @actif is null

begin

select * from @tbl

end

else

SELECT * FROM @tbl where actif = @actif

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 2

Hi,

Ok tanks for your reply but my colleague don't like the test if on stored procedure they prefere AND / OR 🙂

I've done this :

declare @tbl table (id int, nom varchar(20) ,actif bit )

INSERT @tbl values(1, 'un', 1)

INSERT @tbl values(2, 'deux', 0)

INSERT @tbl values(3, 'trois', 1)

INSERT @tbl values(4, 'quatre', 1)

INSERT @tbl values(5, 'cinq', 1)

declare @actif bit = 1

SELECT * FROM @tbl where ( (@actif IS NULL) OR (actif = @actif) )

And that's work 🙂

thanks

christophe

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Ok tanks for your reply but my colleague don't like the test if on stored procedure they prefere AND / OR 🙂

Any specific reason for this preference?

Also check the below mentioned link

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

christophe.bernard 47659 (7/4/2012)
Hi,
Ok tanks for your reply but my colleague don't like the test if on stored procedure they prefere AND / OR 🙂
I've done this :
declare @tbl table (id int, nom varchar(20) ,actif bit )
INSERT @tbl values(1, 'un', 1)
INSERT @tbl values(2, 'deux', 0)
INSERT @tbl values(3, 'trois', 1)
INSERT @tbl values(4, 'quatre', 1)
INSERT @tbl values(5, 'cinq', 1)
declare @actif bit = 1
SELECT * FROM @tbl where ( (@actif IS NULL) OR (actif = @actif) )
And that's work 🙂

It works. It's a performance problem waiting to happen, but it does work.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 5

Hello,

the reason that they don't like the test IF in a stored procedure is due to the performance, they tell me that condition AND OR is better than a IF ..

thanks for your link and your time

I will read all in the day 😉

Christophe

rhythmk SSCertifiable Points: 7271 More actions · Answer 6

christophe.bernard 47659 (7/5/2012)
Hello,
the reason that they don't like the test IF in a stored procedure is due to the performance, they tell me that condition AND OR is better than a IF ..
thanks for your link and your time
I will read all in the day 😉
Christophe

Hey Christophe

I guess you did not see the comment and article posted by Gail.

You must read it before final conclusion 😉

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

christophe.bernard 47659 (7/5/2012)
the reason that they don't like the test IF in a stored procedure is due to the performance

Edit: Yes, they can in some cases (well, not the IF, but the blocks after the IF), but so can their alternative. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

(Edited because I misread the IF solution)

they tell me that condition AND OR is better than a IF ..

It's better if you expect to make money out of performance tuning later... I clean that construct out of my clients' code so often, to huge gains.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

i can see that in some cases using an IF statement might make huge blocks of duplicate code, but i can't see any performance issues

regardless you can do the following (note - can, but not necessarilly should)

select x from y where (actif=@param1 or @param1 is null)

MVDBA