CASE statements are very useful for categorizing data in SQL Server queries. Consider the following SQL statement:
This would yield a resultset like this:
One can easily see that this is much easier than writing a UNION query which would combine several individual queries selecting each price break using a WHERE clause.
SELECT Price as ‘Not Yet Priced', CAST(title AS varchar(20)) AS 'Shortened Title'
In a similar vein, one could use the CASE statement to create columns in a crosstab query when one knows the expected results beforehand:
This query would yield the following result:
CASE has two formats:
The simple CASE function compares an expression to a set of simple expressions to determine the result.
The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument. ntax
Simple CASE function:
The simple case statement allows only an equality check. If the input matches the when expression, the then clause is executed.
Searched CASE function:
The searched case is more powerful (but slightly slower). It allows for complicated Boolean expressions in each when clause.
CASE can be used anywhere an expression can be used: in the SELECT fields like, IN statement, WHERE clauses, and DELETE, APPEND, and UPDATE statements. The only caveat is that expressions used within the CASE statement must be of compatible data types.
Database programming: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, Inc. Home Page
Computer Consulting: Contact Us