CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresweb-applications
user3395937
user3395937

Reputation: 117

Stored procedure that calls a column with bit variable

I am having a bit of trouble understanding the following stored procedure. 

create procedure "abc.PersonReports"
    @PersonId int,
    @Approved bit = null
    ... 
    select 
       ID 'Id',
       StaffID 'Id',
       isnull(Approved, 0) 'Approved',
    ...

I use the following method to call the procedure:

PersonReports = this.personService.GetPersonReports(id, personId, Approved = false)

When I look at the column 'Approved' in the database, it has values of 'null', 0 and 1. These are stated as bit variables. Why is 'Approved' stated as bit = null in the procedure, and what does isnull(Approved, 0) mean? Basically I am trying to alter this procedure to call all rows with the column 'Approved' having the value 'null'.

Upvotes: 1

Views: 229

Answers (2)

Guffa
Guffa

Reputation: 700432

Why is 'Approved' stated as bit = null

That is a default value for the parameter. If the procedure is called without that parameter, it will use the default value instead.

what does isnull(Approved, 0) mean?

It means that the value of the Approved field is selected, and if that value is null then the value 0 is returned instead.

I am trying to alter this proc to call all rows with the column 'Approved' having the value 'null'

For that you would add a condition like this to the query:

where Approved is null

If you want to use the @Approved parameter for the comparison, and allow a null value to get all null values from the table, then you would use:

where (@Approved is null and Approved is null) or (Approved = @Approved)

Upvotes: 0

Belogix
Belogix

Reputation: 8147

Where you see @SomeName in SQL it is a variable. So @Approved bit = null is creating a variable and setting its value to NULL (just initialising to some default value). I am guessing the variable is then set at some other point to TRUE / FALSE or NULL. This bit of code is not shown in your snippet though so assuming in the first ...... section you have.

Then, where you have isnull(Approved, 0) it is saying if Approved is NULL then return the value 0 (FALSE) instead of NULL.

The idea being that Approved can either be TRUE or FALSE and treating NULL as FALSE.

This would beg the question then why allow it to be nullable in the database but that is probably beyond the scope of this question.

Upvotes: 1

Related Questions