IF statement error

Question

I have the following columns in TableA

TableA  
Column1 varchar  
Column2 int  
Column3 bit  

I am using this statement

IF Column3 = 0
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 > 200
ELSE
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 < 200

But the statment does not compile. It says Invalid Column Name 'Column3'

Answer 1

You will need the syntax as follows

IF <CONDITION>
BEGIN
    <Your Statement>
END
ELSE
   <Your Statement>

Hope this is helpful!!

Answer 2

You are mixing 2 different levels:
IF is at the TSQL (procedure) level and cannot depend on the row values
SELECT is the query itself where the row values can be used to filter the result set

The following would work

IF Condition /* independent of the different values of TableA. can be an aggregate though */
BEGIN
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 > 200
END 
ELSE 
BEGIN
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 < 200
END

Answer 3

Assuming you have posted the complete query, then the problem with your IF clause is that you are assuming that it can use the columns from the SELECT statement following it. It cannot and does not. This is why it will not compile.

You need your test condition to be separate from the statements following the IF clause. See on MSDN.

DECLARE @test BIT
SELECT @test = 0

IF @test = 0
 BEGIN
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 > 200
 END
ELSE
 BEGIN
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 < 200
 END

Answer 4

If you want to do this you need to first store the value of Column3 in a variable.

Declare @temp money

Select @Temp = Column3
From TableA


IF @Temp = 0

 begin
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 > 200
 end
ELSE
 begin
  SELECT Column1, Column2 FROM 
  TableA WHERE
  Column2 < 200
 end

Obviously, this assumes that there will only be one value returned for Column3.

EDIT:

This is a different approach which I think should work for you:

declare @CutOffValue money
declare @MaxValue money

Set @CutOffValue = 200
Set @MaxValue = 9999999999

Select Column1, Column2
From TableA
Where Column2 > Case When Column3 = 0 Then @CutOffValue Else 0 End
And Column2 < Case When Column3 = 0 Then @MaxValue Else @CutOffValue End

Answer 5

Why not just do

select Column1, Column2 from TableA where
  Column2 > 200 and Column3 = 0 or Column2 < 200 and Column3 = 1

or, abusing arithmetics,

select Column1, Column2 from TableA where (Column2 - 200) * (2 * Column3 - 1) < 0

Answer 6

Column3 is not being referenced anywhere outside of the IF and ELSE blocks. If you wish to reference this value you will need to declare a new variable and use that;

DECLARE @btColumn3 BIT

SELECT @btColumn3 = Column3 FROM @tblTableA

IF @btColumn3 = 0
  SELECT Column1, Column2 FROM   
  @tblTableA WHERE  
  Column2 > 200  
ELSE
  SELECT Column1, Column2 FROM   
  @tblTableA WHERE  
  Column2 < 200  

Or do the following;

IF (SELECT Column3 FROM @tblTableA) = 0
  SELECT Column1, Column2 FROM   
  @tblTableA WHERE  
  Column2 > 200  
ELSE
  SELECT Column1, Column2 FROM   
  @tblTableA WHERE  
  Column2 < 200  

Either way you will have to ensure that the query used to retrieve Column3 returns a single result either by limiting your query so that it can only return a single value or using MIN(), MAX() etc depending on your requirements.

Also, if you need to execute more than one query within the IF and ELSE blocks you will need to wrap the contents in BEGIN and END as follows:

IF @btColumn3 = 0
BEGIN
  // Do a query
  // Do another
END
ELSE
BEGIN
  // Do a query
  // Do another
END

Answer 7

Since the answer to your problem varies largely on exactly what logic has to be implemented, analyzing present scenario all I can give you is a small and compact query which can meet your requirements (I hope so…)

SELECT Column1,column2 FROM TableA WHERE 
(CASE WHEN Column3=0 then Column2 else 2)>(CASE WHEN Column3=0 then 200 ELSE 1) 
AND (CASE WHEN Column3<>0 then Column2 else 1)<(CASE WHEN Column3<>0 then Column2 else 2)