How can i store the result of a comparison into a variable
I want to print a simple statement print (1=1), i expect the result to be TRUE or 1 but sql server tell me: Incorrect syntax near '='. why is that?
Same will happen for a statement like that
declare @test bit
set @test = (1=1)
in summary how can i "see" what is returned from a comparison without using an IF statement
Update: The reason i'm asking is because i'm trying to debug why the fo开发者_运维知识库llowing statement
declare @AgingAmount smallint
set @AgingAmount = 500
select Amount, datediff(day,Batch.SubmitDate,getdate()) as Aging from myreporrt
where datediff(day,Batch.SubmitDate,getdate()) > @AgingAmount
will return all rows even with aging of 300 so i wanted to test if datediff(day,datesubmited,getdate()) > 500 returns true or false but could not find a way how to display the result of this comparison.
Although SQL Server has the concept of aboolean
type, and it understands expressions that resolve to a boolean
in IF
and WHERE
clauses, it does not support declaring boolean
variables or parameters. The bit
data type cannot store the result of a boolean
expression directly, even though it looks suspiciously like one.
The nearest you can get to a boolean
data type is this:
-- Store the result of a boolean test.
declare @result bit
select @result = case when <boolean expression> then 1 else 0 end
-- Make use of the above result somewhere else.
if @result = 1
...
else
...
To add to the confusion, SQL Server Management Studio treats bit
like boolean
when displaying results, and ADO.NET maps bit
to System.Boolean
when passing data back and forth.
Update: To answer your latest question, use the case when ... then 1 else 0 end
syntax in the select
statement.
精彩评论