w3schools - sql select case




How do I perform an IF…THEN in an SQL SELECT? (16)

A new feature, IIF (that we can simply use), was added in SQL Server 2012:

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

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

For example:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

As an alternative solution to the CASE statement table driven approach can be used.

DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10)) 
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')

SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM 
    @Product P
    LEFT JOIN 
        ( VALUES
            ( 'N', 'Y', 1 )
        ) Stmt (Obsolete, InStock, Saleable)
        ON  P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete 

Result:

ID          Obsolete   InStock    Saleable
----------- ---------- ---------- -----------
1           N          Y          1
2           A          B          0
3           N          B          1
4           A          Y          1

From SQL Server 2012 you can use the IIF function for this.

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product 

This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.

I prefer the conciseness when compared with the expanded CASE version.

Both IIF() and CASE resolve as expressions within a SQL Statement and can only be used in well defined places.

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

If your needs can not be satisfied by these limitations (for example a need to return differently shaped result sets dependant on some condition) then SQL Server does also have a procedural IF Keyword.

IF @IncludeExtendedInformation = 1 
  BEGIN 
      SELECT A,B,C,X,Y,Z 
      FROM   T 
  END 
ELSE 
  BEGIN 
      SELECT A,B,C 
      FROM   T 
  END 

Care must sometimes be taken to avoid parameter sniffing issues with this approach however.


From this link, we can uderstand IF THEN ELSE in T-SQL :

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'ALFKI')
  PRINT 'Need to update Customer Record ALFKI'
ELSE
  PRINT 'Need to add Customer Record ALFKI'

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'LARSE')
  PRINT 'Need to update Customer Record LARSE'
ELSE
  PRINT 'Need to add Customer Record LARSE' 

Isn't this good enough for T-SQL ?


Microsoft SQL Server (T-SQL)

In a select use:

select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end

In a where clause, use:

where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end

Question ... SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

ANSI: Select case when p.Obsolete = 'N' or p.InStock = 'Y' then 1 else 0 end as Saleable, p.* FROM Product p;

Using aliases -- p in this case -- will help prevent issues.


Simple if-else statement in SQL Server:

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';

GO

Nested If...else statement in sql server -

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
  PRINT 'what''s up?';
ELSE
  PRINT 'Bye Ravi Anand.';
END;

GO

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 do the CAST 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

This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.

 CASE orweb2.dbo.Inventory.RegulatingAgencyName
    WHEN 'Region 1'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 2'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 3'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'DEPT OF AGRICULTURE'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
    ELSE (
            CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
                WHEN 1
                    THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
                ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
                END
            )
    END AS [County Contact Name]

Use CASE. Something like this.

SELECT Salable =
        CASE Obsolete
        WHEN 'N' THEN 1
        ELSE 0
    END

Use pure bit logic:

DECLARE @Product TABLE (
    id INT PRIMARY KEY IDENTITY NOT NULL
   ,Obsolote CHAR(1)
   ,Instock CHAR(1)
)

INSERT INTO @Product ([Obsolote], [Instock])
    VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')

;
WITH cte
AS
(
    SELECT
        'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
       ,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
       ,*
    FROM
        @Product AS p
)
SELECT
    'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
   ,*
FROM
    [cte] c

See working demo: IF THEN WITHOUT CASE IN MSSQL

For start, you need to work out the value of true and false for selected conditions. Here comes two NULLIF:

for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)

combined together gives 1 or 0. Next use bitwise operators.

It's the most WYSIWYG method.


You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):

SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees

 SELECT
   CASE 
      WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' 
      ELSE 'FALSE' 
   END AS Salable,
   * 
FROM PRODUCT

SELECT  
(CASE 
     WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
                                            ELSE 'NO' 
 END) as Salable
, * 
FROM Product

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

SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile




case