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
Related Posts:
- Inserting multiple rows in a single SQL query? [duplicate]
- T-SQL split string
- SQL Server IF EXISTS THEN 1 ELSE 2
- SQL Server : Arithmetic overflow error converting expression to data type int
- How to calculate percentage with a SQL statement
- What is the meaning of the prefix N in T-SQL statements and when should I use it?
- Query error with ambiguous column name in SQL
- How to group by month from Date field using sql
- How can I get column names from a table in SQL Server?
- Is there a combination of “LIKE” and “IN” in SQL?
- How to format a numeric column as phone number in SQL
- SQL: IF clause within WHERE clause
- Selecting COUNT(*) with DISTINCT
- Case in Select Statement
- What is a stored procedure?
- Self Join to get employee manager name
- How do I UPDATE from a SELECT in SQL Server?
- SQL query to select dates between two dates
- When should I use CROSS APPLY over INNER JOIN?
- SQL query to get the employee name and their manager name from the same table
- How can I do an UPDATE statement with JOIN in SQL Server?
- The SQL OVER() clause – when and why is it useful?
- Update multiple columns in SQL
- SQL Query with NOT LIKE IN
- How do I escape a single quote in SQL Server?
- T-SQL split string based on delimiter
- SQL Query with SUM with Group By
- How do I escape a single quote in SQL Server?
- IDENTITY_INSERT is set to OFF – How to turn it ON?
- SQL update from one Table to another based on a ID match
- Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
- Each GROUP BY expression must contain at least one column that is not an outer reference
- How to drop all tables from a database with one SQL query?
- Column name or number of supplied values does not match table definition
- SQL Server FOR EACH Loop
- Query comparing dates in SQL
- “CASE” statement within “WHERE” clause in SQL Server 2008
- How to drop all tables from a database with one SQL query?
- How to find sum of multiple columns in a table in SQL Server 2005?
- Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint
- Keeping it simple and how to do multiple CTE in a query
- How can I delete using INNER JOIN with SQL Server?
- Why use a READ UNCOMMITTED isolation level?
- Find all tables containing column with specified name – MS SQL Server
- DateTime2 vs DateTime in SQL Server
- Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
- CREATE TABLE IF NOT EXISTS equivalent in SQL Server
- SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
- How to get a date in YYYY-MM-DD format from a TSQL datetime field?
- How to parse XML data in SQL server table
- Search text in stored procedure in SQL Server
- What is the equivalent of ‘describe table’ in SQL Server?
- Error converting data type varchar to float
- MS SQL compare dates?
- MS SQL compare dates?
- I want to use CASE statement to update some records in sql server 2005
- What represents a double in sql server?
- Get size of all tables in database
- Using RegEx in SQL Server
- Conversion failed when converting the varchar value ‘simple, ‘ to data type int
- SELECT DISTINCT on one column
- Exclude a column using SELECT * [except columnA] FROM tableA?
- Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
- TSQL PIVOT MULTIPLE COLUMNS
- SQL – WHERE (X, Y) IN (A, B)
- SQL “select where not in subquery” returns no results
- Equivalent of Oracle’s RowID in SQL Server
- sql query to return differences between two tables
- How do I format a number with commas in T-SQL?
- Is it possible to specify condition in Count()?
- SQL Server dynamic PIVOT query?
- Error converting data type varchar
- Temporary table in SQL server causing ‘ There is already an object named’ error
- I want to use CASE statement to update some records in sql server 2005
- To add server using sp_addlinkedserver
- How to calculate age (in years) based on Date of Birth and getDate()
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified
- Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
- How can I group by date time column without taking time into consideration
- How to select the last record of a table in SQL?
- SQL- Ignore case while searching for a string
- Comma separated results in SQL
- “select * into table” Will it work for inserting data into existing table
- SUM OVER PARTITION BY
- Update query using Subquery in Sql Server
- Varchar invalid for Sum operator
- Using Excel VBA to run SQL query
- What is tableName.* in SQL
- How to create Temp table with SELECT * INTO tempTable FROM CTE Query
- CREATE VIEW must be the only statement in the batch
- SQL Server Insert if not exists
- SQL Server: Invalid Column Name
- sql server invalid object name – but tables are listed in SSMS tables list
- Count(*) vs Count(1) – SQL Server
- Difference between database and schema
- SQL Server Linked Server Example Query
- SQL datetime compare
- How to write a foreach in SQL Server?
- SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
- Get everything after and before certain character in SQL Server