CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlbit
G21
G21

Reputation: 1345

T-SQL - Bit Output Parameter Default Value

I'm currently investigating a bug in a stored procedure build over MS SQL Server 2008. Current stored procedure signature looks like: 

CREATE PROCEDURE dbo.<Name>
(
    ParameterOne int
    , ParameterTwo bit output
)

In the code the output parameter value is being evaluated to determine code flows which is at least suspicious to me. Code looks like this: 

IF ( ParameterTwo = 0 )
BEGIN
      PRINT ParameterTwo
      -- Do stuffs
END

IF ( ParameterTwo = 0)
BEGIN
      PRINT ParameterTwo
      -- Do other stuffs
END

On debugging (using PRINTs) I configured flags to be displayed inside the block #1 and block # 2 but none of them were shown when executed the code. However, if you assign a value to ParameterTwo ( ParameterTwo = 0 ) right before the block #1 is executed, the flags are being displayed as expected.

My concern is obviously about bit output parameter creation. When you create a bit parameter (in/out) which is the default value being assigned by MS SQL Server(if any)?. Is that a NULL value? I was under the impression it was a 0 (false) but it does not seems so.

Btw - I'm considering to avoid the output parameter usage withing the code for comparison purposes. Instead, I would tend to think there would be more clear to use internal variable and assign its value to the output parameter before stored procedure ends.

Any responses/comments will be appreciated.

Thanks in advance.

Upvotes: 0

Views: 6125

Answers (2)

Paul Williams
Paul Williams

Reputation: 17020

SQL does not default parameters unless you specify a default. If you do not specify a default, the parameter value is NULL.

For example, the output of all the PRINT statements below is "@bitX is null":

create procedure sp_testbit
(
    @bit1 bit,
    @bit2 bit output
)
as begin
    if @bit1 is null print 'sp_testbit @bit1 is null'
    else print 'sp_testbit @bit1 = ' + cast (@bit1 as varchar)

    if @bit2 is null print 'sp_testbit @bit2 is null'
    else print 'sp_testbit @bit2 = ' + cast (@bit2 as varchar)
end
go

declare @bit1 bit, @bit2 bit
exec sp_testbit @bit1, @bit2 output

if @bit1 is null print 'exec sp_testbit @bit1 is null'
else print 'exec sp_testbit @bit1 = ' + cast (@bit1 as varchar)

if @bit2 is null print 'exec sp_testbit @bit2 is null'
else print 'exec sp_testbit @bit2 = ' + cast (@bit2 as varchar)
go

drop procedure sp_testbit
go

You can declare a parameter and assign it a default value at the same time. This makes it optional to pass the input parameters:

create procedure sp_testbit
(
    @bit1 bit = 0,
    @bit2 bit = 0 output
)

If you don't want the input parameters to be optional, you can explicitly set the values in the stored procedure code:

create procedure sp_testbit
(
    @bit1 bit,
    @bit2 bit output
)
as begin
    if @bit1 is null set @bit1 = 0 -- default the input bit
    set @bit2 = 0                  -- default the output bit

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The documentation is quite clear on this point:

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a valu is provided as part of the declaration.

This is also true of columns in tables. And it applies to bits as well as other types.

Note that a NULL value fails all comparisons (except is null), so it is not equal to either 0 or 1.

Upvotes: 3

Related Questions