Using If Else in SQL Select Statement

Using If else in SQL Select statement

you can use CASE

SELECT   ...,
         CASE WHEN IDParent < 1 THEN ID ELSE IDPArent END AS ColumnName,
         ...
FROM     tableName

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

SQL SELECT statement within an IF statement

The reason is that the Exists function is checking the result of the sub-query for existing - are there any rows or not. And, as you return the COUNT, it'll never be not-existing - COUNT returns 0 if there are no rows presented in database.

Try to store the resulting count in a local variable, like in this question:

Using IF ELSE statement based on Count to execute different Insert statements

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END

Statement blocks in SQL SELECT using IF ELSE

You can use an IF statement, but you'll need to set up multiple queries. You can use a CASE for selecting one column or another, but not to select one or multiple like in your question.

DECLARE @var INT = 1;

DECLARE @test TABLE (
    Col1 int,
    Col2 int,
    Col3 int
)

INSERT INTO @test VALUES (1,2,3)

IF @var = 1
BEGIN
    SELECT  Col1, Col2
    FROM    @test
END
ELSE
BEGIN
    SELECT  Col3
    FROM    @test
END

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'

IF' in 'SELECT' statement - choose output value based on column values

SELECT id, 
       IF(type = 'P', amount, amount * -1) as amount
FROM report

See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

Additionally, you could handle when the condition is null. In the case of a null amount:

SELECT id, 
       IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

Set a Variable Using IF/ELSE That Contains SELECT Statement

Looking at your query, the 'SET @errorMsg' might just be in the wrong place.

declare @errorMsg nvarchar(MAX)
declare @combinedString VARCHAR(MAX)

IF EXISTS (select y from db.b)
    BEGIN
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select CONCAT(cg.x, '-', f.y) AS x_y
        from db.a cg
        LEFT JOIN db.b f
        ON cg.x = f.y) AS w;
        SET @errorMsg ='The x listed in the database are (x - y if applicable): ' + @combinedString
    END
ELSE
    BEGIN
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select cg.x AS x_y
        from db.a) AS w;
        SET @errorMsg = 'The x listed in the database are (x - y if applicable): ' + @combinedString
    END

---

Related Topics