Default value for boolean parameter

Transact-SQL
1

This works pretty fine

ALTER Procedure [dbo].[sp_tblMySp_sel]
@Na_Id int =0
As
Begin
Select MyField
From MyTable
where (Na_Id=@Na_Id or @Na_Id=0)
end

Now I would like to do the same but with a boolean field

ALTER Procedure [dbo].[sp_tblMySp_sel]
@Na_YesNo bit = ??
As
Begin
Select MyField
From MyTable
where (Na_YesNo=@Na_YesNo or @Na_YesNo=??)
end

I tried several values as a default value but couldn't find one. Any idea ?

2

bit can hold 0 , 1 or NULL

@Na_YesNo bit = 0
3

When you specify

@Na_YesNo bit = 0

It will give me only rows where Na_YesNo is false, say 10 rows

@Na_YesNo bit =1

It will give me only rows where Na_YesNo is true, say 15 rows

@Na_YesNo bit =null

Just gives me no row at all.

What I want to achieve is that when no parameter is passed down to @Na_YesNo the select statement to retrieve all rows therefore, I am expecting 25 rows

4

when you compare anything with NULL, the result is FALSE

you will need to handle NULL specifically, like

(@Na_YesNo IS NULL AND Na_YesNo IS NULL)
1 Like
5

I am not sure this is going to work. 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
6

your testing data does not have any record with YesNo value = NULL

add this

INSERT INTO #temp (Id, Name,YesNo) 
Select 5, 'e', NULL

the WHERE condition is wrong. Change to

WHERE YesNo = @YesNo
	OR (
		@YesNo IS NULL
		AND YesNo IS NULL
		)
7

My data do not have record with null values and it is never going to happen. The idea of my stored procedure is to retrieve records either true or false when the parameter is specified or all if no parameter is specified.

8 
where (Na_YesNo=@Na_YesNo or @Na_YesNo IS NULL)