Default value for boolean parameter

Question

Default value for boolean parameter

Corobori

Ten Centuries

Points: 1316
More actions
September 23, 2015 at 4:55 am

#308018

Here is my issue: I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)
Select 2, 'b', 0
INSERT INTO #temp (Id, Name,YesNo)
Select 3, 'c', 1
INSERT INTO #temp (Id, Name,YesNo)
Select 4, 'd', 0
CREATE Procedure [dbo].[#sp_tblMySp_sel]
@YesNo bit = null
As
Begin
declare @iCount int
Select @iCount=count(Id)
From #temp
where (YesNo=@YesNo or @YesNo=null)
print @iCount
end
#sp_tblMySp_sel 1
go
#sp_tblMySp_sel 0
go
#sp_tblMySp_sel
go
drop table #temp
drop procedure #sp_tblMySp_sel

Jean-Luc
www.corobori.com

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

or @YesNo IS null)

I wish that SQL Server have a mode/flag/option that would moan about things like this ... on the occasions that they take hours to find the time wasted is very annoying ...

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

Word of advice:

CREATE TABLE #temp

( Id int, Name char(30), YesNo bit NOT NULL)

Also you may want to review the first section of this article, before your coding pattern causes major problems

https://www.simple-talk.com/content/article.aspx?article=2280

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Kristen-173977 (9/23/2015)
or @YesNo IS null)
I wish that SQL Server have a mode/flag/option that would moan about things like this ... on the occasions that they take hours to find the time wasted is very annoying ...

Check out SET ANSI_NULLS OFF behaviour.

Until that setting is fully deprecated, I don't think that SQL Server could possibly moan about this 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Phil Parkin (9/23/2015)
Until that setting is fully deprecated, I don't think that SQL Server could possibly moan about this 🙂

I'm not letting SQL off the hook 😛 - it is parsing the code so it should know what the state of ANSI_NULLS is AFAIAC 😎 Failing that some sort of run-time warning-logging/reporting/ABSOLUTELY ANYTHING!! would do me.

Second best is code validation akin to SQL LINT, its just that Warnings would catch things sooner than Lint would, as I only run the Lint-type tool we use close to moving from DEV to QA Testing as a final belt-and-braces check - so although I expect it would catch this, I would have most probably encountered it in development and wasted the time already.

I'm blowed if I can remember, without looking it up, what the Lint-like tool we use for code validation is called.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

Just need to change

where (YesNo=@YesNo or @YesNo=null)

to this...

where (YesNo=@YesNo or @YesNo IS NULL)

OPTION(RECOMPILE);