SQL Conditional Select

SQL conditional SELECT

Sounds like they want the ability to return only allowed fields, which means the number of fields returned also has to be dynamic. This will work with 2 variables. Anything more than that will be getting confusing.

IF (selectField1 = true AND selectField2 = true)
BEGIN
   SELECT Field1, Field2
   FROM Table
END
ELSE IF (selectField1 = true)
BEGIN
   SELECT Field1
   FROM Table
END
ELSE IF (selectField2 = true)
BEGIN
   SELECT Field2
   FROM Table
END

Dynamic SQL will help with multiples. This examples is assuming atleast 1 column is true.

DECLARE @sql varchar(MAX)
SET @sql = 'SELECT '
IF (selectField1 = true)
BEGIN
   SET @sql = @sql + 'Field1, '
END
IF (selectField2 = true)
BEGIN
   SET @sql = @sql + 'Field2, '
END
...
-- DROP ', '
@sql = SUBSTRING(@sql, 1, LEN(@sql)-2)

SET @sql = @sql + ' FROM Table'

EXEC(@sql)

Conditional Select from different tables

You'll want to use a UNION:

SELECT a.cnt, b.serial, b.description
  FROM a INNER JOIN b
    ON a.key = b.key
 WHERE a.cnt > 0
 UNION ALL
SELECT a.cnt, c.serial, c.description
  FROM a INNER JOIN c
    ON a.key = c.key
 WHERE a.cnt <= 0;

Hope this helps.

SQL conditional select with for/while loop

One way to do it is like this:

select order_id || ' - ' || status
from (
    select order_id, priority, status,
           rank() over (partition by order_id order by priority desc) ranking
    from table
)
where ranking = 1;

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

Conditional SELECT in BIgQuery/SQL

Does case do what you want?

(CASE WHEN Activity_ID IN ('9024844', '9033733', '9022293', '9062686') THEN Activity_ID) as Sales,
(CASE WHEN Activity_ID IN ('9024799', '9001112', '9001133, '9021942') THEN Activity_ID END) as Leads,

Note: I might suggest just using a boolean column as a flag:

(Activity_ID IN ('9024844', '9033733', '9022293', '9062686')) as is_sale,
(Activity_ID IN ('9024799', '9001112', '9001133', '9021942')) as is_Lead,

Conditional Select Statement in T-SQL

Why not add an

and name is not null

to the where clause?

---

Related Topics