CODEWITHSUNDEEP
sql-server
Leo Sam
Leo Sam

Reputation: 83

how to use a bit type variable as precondition for a criteria in where clause in SQL server

I have a bit type variable @ExcludeCB which allows user to exclude a type of transactions from query result. The question is that how I can combine this condition if @ExcludeCB = 1 then TN.TT !='CB' to other conditions? I used the CASE WHEN but it reflected Error. Here is a part of the code:

declare @FVoucher as int; ---- first Accounting Voucher No

declare @LVoucher as int; ---- last Accounting Voucher No

declare @ExcludeCB as bit; ---- excluding Transaction type CB


set @FVoucher = 2004002;

set @LVoucher = 2004120;

set @ExcludeCB =1 


WITH ctOB as  ---- making a TB prior to the report range

( Select GLcode, GLname, Sum((case WHEN amount>0 then Amount ELSE 0 END)) as obDebit , 

    Sum((Case When Amount <0 then - Amount ELSe 0 END)) as obCredit

    From TN 

        WHERE Year(tn.GDate) = Convert(int,substring(Convert(varchar,@fVoucher),1,4)) and tn.VoucherNo < @FVoucher

        AND Case @ExcludeCB when 1 then tn.tt !='CB' ELSE 1 = 1 END

    group by GLcode, GLname

) ,

Upvotes: 0

Views: 773

Answers (2)

Alberto Martinez
Alberto Martinez

Reputation: 2670

It seems that your are trying to get two different conditions with the CASE statement, but you can't do that because all the parts of a CASE must return a value or NULL (something that you can use as a column value), and in SQL Server you don't have a boolean type for variables/columns.

SQL Server has the bit type which looks like a boolean but is different, because its possible values are 0/1, nor true/false. With real boolean types you can use if boolean_varible=true or better if boolean_variable to get a boolean condition, but with a bit type you always have to use if bit_variable=1.

Returning to your question, in order to get what you want you can replace:

AND Case @ExcludeCB when 1 then tn.tt !='CB' ELSE 1 = 1 END

with something like this:

AND ((@ExcludeCB=1 and tn.tt !='CB') or @ExcludeCB=0)

Upvotes: 3

Dan Guzman
Dan Guzman

Reputation: 46213

CASE is an expression rather than a statement in SQL Server. You'll need to include the CASE expression in a predicate for use in the WHERE clause. The method below uses the simple form of CASE instead of the searched CASE you were attempting:

AND CASE WHEN @ExcludeCB = 1 AND tn.tt <> 'CB' THEN 1 END = 1

The result of this CASE expression will be 1 when @ExcludeCB is 1 and tn.tt is not equal to 'CB'. The CASE result will then be compared to the literal 1 in the predicate, resulting in a true WHERE clause condition. The CASE expression will otherwise return NULL, resulting in an unknown predicate because anything compared to NULL is unknown. No rows will then satisfy the condition because unknown is not true.

You could also use OR to express the condition without a CASE expression. This example considers the possibility of a NULL @ExcludeCD value:

AND (ISNULL(@ExcludeCB,0) = 0 OR (@ExcludeCB = 1 AND tn.tt <> 'CB'))

Upvotes: 0

Related Questions