Sql Server 2008 - Case/If Statements in Select Clause

SQL Server 2008 - Case / If statements in SELECT Clause

Just a note here that you may actually be better off having 3 separate SELECTS for reasons of optimization. If you have one single SELECT then the generated plan will have to project all columns col1, col2, col3, col7, col8 etc, although, depending on the value of the runtime @var, only some are needed. This may result in plans that do unnecessary clustered index lookups because the non-clustered index Doesn't cover all columns projected by the SELECT.

On the other hand 3 separate SELECTS, each projecting the needed columns only may benefit from non-clustered indexes that cover just your projected column in each case.

Of course this depends on the actual schema of your data model and the exact queries, but this is just a heads up so you don't bring the imperative thinking mind frame of procedural programming to the declarative world of SQL.

How do I perform an IF...THEN in an SQL SELECT?

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Select with CASE Statement SQL Server 2008 R2

The problem is that you are including the calculated PRIN_DX_ columns in the aggregation. Instead, remove them from the aggregation and just choose the non-0 value (using max()):

SELECT C.PT_NO, C.MED_REC_NO, C.PT_NAME,
       max(C.PRIN_DX_CD_1) as PRIN_DX_CD_1,
       max(C.PRIN_DX_CD_2) as PRIN_DX_CD_2,
       max(C.PRIN_DX_CD_3) as PRIN_DX_CD_3,
       max(C.PRIN_DX_CD_4) as PRIN_DX_CD_4,
       max(C.PRIN_DX_CD_5) as PRIN_DX_CD_5,
       (case when max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) + 
                  max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5) < 6
             then max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) + 
                  max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5)
             else 6
        end) as CC_LACE_SCORE
FROM (SELECT DISTINCT PAV.PT_NO, MED_REC_NO, PT_NAME,
             (CASE WHEN dv.ClasfCd IN ()
                   THEN 1
                   ELSE 0
              END) AS PRIN_DX_CD_1,
             (CASE WHEN DV.ClasfCd IN ()
                   THEN 2
                   ELSE 0
              END) AS PRIN_DX_CD_2
             (CASE WHEN DV.ClasfCd IN ()
                   THEN 3
                   ELSE 0
              END) AS PRIN_DX_CD_3,
             (CASE WHEN DV.ClasfCd IN ()
                   THEN 4
                   ELSE 0
              END) AS PRIN_DX_CD_4,
             (CASE WHEN DV.ClasfCd IN ()
                   THEN 6
                   ELSE 0
              END) AS PRIN_DX_CD_5   
      FROM smsdss.BMH_PLM_PtAcct_V PAV join
           smsdss.BMH_PLM_PtAcct_Clasf_Dx_V DV
           ON PAV.PtNo_Num = DV.PtNo_Num
      WHERE Dsch_Date BETWEEN @SD AND @ED
     ) C
GROUP BY C.PT_NO, C.MED_REC_NO, C.PT_NAME
ORDER BY C.Pt_No;

I suspect the distinct in the subquery may not be necessary, but that depends on what your data really looks like.

How do you write an IF ELSE inside a SELECT statement in MSSQL 2008?

Use CASE ... WHEN. The most concise logic seems to be:

SELECT 
  CASE WHEN d.ItemType IN ('INVOICE', 'PACKING') THEN 0 ELSE 1 END
     AS MissingDocuments 
FROM dbo.DocumentDetails AS d

i.e. the Document is missing if it isn't 'Invoice' or 'Packing'

Is it possible to put a select statement in a case statement?

I have found the solution to my specific problem. it was just my where clause causing added rows.

But to answer the original question of can you put a subquery select statement in a CASE. YES you can do it.

CASE
   WHEN condition
   THEN (select column1, from tbl join tbl2 on tbl1.column = tbl2.column)

   ELSE 
    (whatever)

END

SQL Switch/Case in SQL Server 2008 R2

Use the condition form of case:

SELECT (CASE WHEN @TestVal IN (1, 2, 6) THEN 'First' 
             WHEN @TestVal = 3 THEN 'Second'
             WHEN @TestVal = 4 THEN 'Third'
             ELSE 'Other'
        END)

Using Case IF statements in queries

You can do this with three conditions in your WHERE clause:

  select COUNT(*) from Employee emp  
  where 
  (@@SERVERNAME = 'Server1' AND emp.LocationID in (1,2,3))
    OR 
  (@@SERVERNAME = 'Server2' AND emp.LocationID in (4,5,6))
    OR 
  (@@SERVERNAME = 'Server3' AND emp.LocationID in (7,8,9))

TSQL CASE with if comparison in SELECT statement

Please select the same in the outer select. You can't access the alias name in the same query.

SELECT *, (CASE
        WHEN articleNumber < 2 THEN 'Ama'
        WHEN articleNumber < 5 THEN 'SemiAma' 
        WHEN articleNumber < 7 THEN 'Good'  
        WHEN articleNumber < 9 THEN 'Better' 
        WHEN articleNumber < 12 THEN 'Best'
        ELSE 'Outstanding'
        END) AS ranking 
FROM(
    SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber, 
    hobbies, etc...
    FROM USERS
)x

Case statement in where clause in SQL Server if variable have any value otherwise compare current

try this way:

SELECT a.user_id, a.username, a.first_name, a.last_name, a.division_id, a.dept_id, a.email,
a.password, a.IsAdmin, a.status, a.cdate, a.mdate FROM Account AS a 
LEFT JOIN Roles r on a.user_id = r.user_id
WHERE ((ISNULL(@role_id,'') = '') OR (r.role_id = @role_id))

How do I do multiple CASE WHEN conditions using SQL Server 2008?

There are three formats of case expression. You can do CASE with many WHEN as;

CASE  WHEN Col1 = 1 OR Col3 = 1  THEN 1 
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

Or a Simple CASE expression

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END

Or CASE within CASE as;

CASE  WHEN Col1 < 2 THEN  
                    CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

---

Related Topics