using an if statement or case statement in a where clause sql 2012

Question

I have a situation in SQL server 2012 and I need to query a different column in a table based on a variable. So I have a table and the columns have a bld_type field and 3 additional columns that are specific to the year. The columns with the year contain either an A or I.
So the table is set up like this

testtable

bld_type   bld_2015  bld_2016 bld_2017
123            A        A      I
124            A        A      I
126            I        A      I

I have a stored procedure that is going to compare a variable that I get from an outside source to the bld_type based on the year that the project is produced.

I have tried if year = 2015 then select bld_type from testtable where bld_2015 = 'A' and this works fine and I have even said else and had a different select statement checking bld_2016 = 'A'.

The rub comes in when I try to put this into another if construct. The first example would have produced 123,124. And what I need to do is add the code to this if statement

if @TRM_type not in (my code from above)
begin @errorcount = @errorcount + 1 end

In other words, when I try to embed an if statement inside an if statement it does not want to work. Am I missing a bracket or an end in some way?

Thanks in advance for any help.

We need to see your code to have any chance of helping you here. And if you are going to post table data it needs to be formatted so we can decipher it. The title of your question suggests there is a major problem because you can't put an if statement in a where clause. — Sean Lange, Nov 16 '16 at 22:07

score 1 · Answer 1 · edited May 23 '17 at 12:30

I would move the @TRM_type into the where clause as well it should be more performant and then just use exists:

IF NOT EXISTS (
    SELECT
       1
    FROM
       TestTable
    WHERE
       CASE @year
          WHEN 2015 THEN bld_2015
          WHEN 2016 THEN bld_2016
          WHEN 2017 THEN bld_2017
       END = 'A'
       AND bld_type = @TRM_type
)
BEGIN
    SET @errorcount = @errorcount + 1
END

The problem with IN is if your inner query could ever produce a Null you will not get the result you want as SQL engine wont no how to interpret @var in (NULL) and will return everything. Plus IN is typically the slowest method for SQL-server (NOT IN vs NOT EXISTS)

As far as embedding and IF statement into a where clause on SQL 2012 plus. It is not the same as a control IF instead it is a function IIF(condition, true, false) https://msdn.microsoft.com/en-us/library/hh213574.aspx so to "embed it" or rather use it in a query you would do something like this:

SELECT *
FROM
   testtable
WHERE
   IIF(@year = 2016, bld_2016, NULL) = 'A'

But because you have more than 2 cases (2015,206,2017) a CASE expression is best.

And about nesting IFs

IF (condition - Scalar Value That Evaluates to True)
BEGIN

  IF (Condition)
  BEGIN
      --Do Something
  END

END

But you can NOT nest IF's like this:

IF (IF BEGIN do something END)
BEGIN

END

One of the reasons is because the IF as the condition has no way of passing a scalar value to evaluate as true or false.

score 0 · Answer 2 · answered Nov 16 '16 at 22:16

I hate having CASE statements within a WHERE clause, but this should get you the build types that you want.

SELECT bld_type FROM testtable WHERE (CASE @year WHEN 2015 THEN bld_2015
                                                 WHEN 2016 THEN bld_2016
                                                 WHEN 2017 THEN bld_2017
                                                 ELSE NULL END) = 'A'

Then...

if @TRM_type not in (SELECT bld_type 
                       FROM testtable 
                       WHERE (CASE @year WHEN 2015 THEN bld_2015
                                         WHEN 2016 THEN bld_2016
                                         WHEN 2017 THEN bld_2017
                                         ELSE NULL END) = 'A')
begin @errorcount = @errorcount + 1 end

using an if statement or case statement in a where clause sql 2012

2 Answers2