{"id":11151,"date":"2020-11-18T09:17:50","date_gmt":"2020-11-17T23:17:50","guid":{"rendered":"https:\/\/database.guide\/?p=11151"},"modified":"2020-11-18T09:17:51","modified_gmt":"2020-11-17T23:17:51","slug":"sql-server-case-expression","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-server-case-expression\/","title":{"rendered":"SQL Server CASE Expression"},"content":{"rendered":"\n<p>In SQL Server, the T-SQL <code>CASE<\/code> expression is a scalar expression that returns a value based on conditional logic. It evaluates a list of conditions and returns a value, based on the outcome of those conditions..<\/p>\n\n\n\n<p>In some ways, the SQL Server <code>CASE<\/code> expression is similar to <code>IF...ELSE<\/code>. However, <code>CASE<\/code> allows you to check for multiple conditions, whereas <code>IF...ELSE<\/code> doesn&#8217;t. <\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Also, in SQL Server, <code>IF...ELSE<\/code> is a control-of-flow language keyword, whereas <code>CASE<\/code> is not. The <code>CASE<\/code> expression cannot be used to control the flow of execution of <a href=\"https:\/\/database.guide\/what-is-t-sql\/\" title=\"What is T-SQL?\">T-SQL<\/a> statements, statement blocks, user-defined functions, and stored procedures.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The 2 Forms of CASE Expression<\/h2>\n\n\n\n<p>There are two forms of <code>CASE<\/code> expression in SQL Server:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Simple <code>CASE<\/code> expression<\/li><li>Searched <code>CASE<\/code> expression<\/li><\/ul>\n\n\n\n<p>These are explained with examples below.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Form 1 &#8211; The Simple CASE Expression<\/h2>\n\n\n\n<p>The simple <code>CASE<\/code> expression compares an expression to a set of simple expressions to determine the result.<\/p>\n\n\n\n<p>Here&#8217;s a basic example to demonstrate how a <code>CASE<\/code> expression works in SQL Server.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @stock_ticker varchar(4) = 'V';\n\nSELECT Company =  \n    CASE @stock_ticker  \n        WHEN 'AAPL' THEN 'Apple'\n        WHEN 'FB' THEN 'Facebook'\n        WHEN 'V' THEN 'Visa'\n        ELSE 'Not in the portfolio'  \n    END<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------+\n| Company &nbsp; |\n|-----------|\n| Visa&nbsp; &nbsp; &nbsp; |\n+-----------+<\/pre>\n\n\n\n<p>In this example, my <code>CASE<\/code> expression is part of a <code>SELECT<\/code> statement. It checks for three conditions and has an <code>ELSE<\/code> to cater for anything that isn&#8217;t covered in the three conditions. <\/p>\n\n\n\n<p>In this case, the stock ticker <code>V<\/code> matches the third <code>WHEN<\/code> expression, and the expression provided by <code>THEN<\/code> is returned.<\/p>\n\n\n\n<p>To be clear, the actual <code>CASE<\/code> expression is this part:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    CASE @stock_ticker  \n        WHEN 'AAPL' THEN 'Apple'\n        WHEN 'FB' THEN 'Facebook'\n        WHEN 'MA' THEN 'Mastercard'\n        WHEN 'V' THEN 'Visa'\n        ELSE 'Not in the portfolio'  \n    END<\/code><\/pre>\n\n\n\n<p>What <code>CASE<\/code> does is, it checks the value of each <code>WHEN<\/code> expression against the input expression. In my example, the <code>@stock_ticker<\/code> variable is the input expression. Therefore, it&#8217;s checking the value of each <code>WHEN<\/code> expression against the <code>@stock_ticker<\/code> variable.<\/p>\n\n\n\n<p>When\/if it finds a match, then it returns the expression provided by <code>THEN<\/code>. <\/p>\n\n\n\n<p>My example uses three <code>WHEN<\/code> expressions, but it could have been more and it could have been less, depending on my requirements. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Form 2 &#8211; The Searched CASE Expression<\/h2>\n\n\n\n<p>The searched <code>CASE<\/code> expression evaluates a set of Boolean expressions to determine the result.<\/p>\n\n\n\n<p>Here&#8217;s an example of a searched <code>CASE<\/code> expression.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @price int = 1500;\n\nSELECT Affordability =  \n    CASE   \n        WHEN @price &lt; 100 THEN 'Cheap'\n        WHEN @price >= 100 AND @price &lt; 500 THEN 'Affordable'\n        ELSE 'Expensive'  \n    END<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------+\n| Affordability &nbsp; |\n|-----------------|\n| Expensive &nbsp; &nbsp; &nbsp; |\n+-----------------+<\/pre>\n\n\n\n<p>A searched <code>CASE<\/code> expression doesn&#8217;t have an input expression like the simple <code>CASE<\/code> expression.<\/p>\n\n\n\n<p>You&#8217;ll recall that in our simple <code>CASE<\/code> expression, it started off with <code>CASE<\/code> <code>@stock_ticker<\/code>, and therefore we knew that the <code>WHEN<\/code> expressions were all evaluating against the value of <code>@stock_ticker<\/code>.<\/p>\n\n\n\n<p>With the searched <code>CASE<\/code> expression, we don&#8217;t provide an input expression at the start like that. Instead, each <code>WHEN<\/code> expression includes a Boolean expression for which to be evaluated against.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">A Database Example<\/h2>\n\n\n\n<p>Here&#8217;s an example that demonstrates how the <code>CASE<\/code> expression can be used within a database query.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE WideWorldImporters;\nSELECT \n    CityName AS &#91;City], \n    LatestRecordedPopulation AS &#91;Population], \n    Size =  \n      CASE \n         WHEN LatestRecordedPopulation &lt; 2000000 THEN 'Small City'  \n         WHEN LatestRecordedPopulation >= 2000000 AND LatestRecordedPopulation &lt; 3000000 THEN 'Big City' \n         ELSE 'Really Big City'\n      END \nFROM Application.Cities\nWHERE LatestRecordedPopulation > 1000000;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+--------------+--------------+-----------------+\n| City &nbsp; &nbsp; &nbsp; &nbsp; | Population &nbsp; | Size&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |\n|--------------+--------------+-----------------|\n| Brooklyn &nbsp; &nbsp; | 2565635&nbsp; &nbsp; &nbsp; | Big City&nbsp; &nbsp; &nbsp; &nbsp; |\n| Chicago&nbsp; &nbsp; &nbsp; | 2695598&nbsp; &nbsp; &nbsp; | Big City&nbsp; &nbsp; &nbsp; &nbsp; |\n| Dallas &nbsp; &nbsp; &nbsp; | 1197816&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| Houston&nbsp; &nbsp; &nbsp; | 2099451&nbsp; &nbsp; &nbsp; | Big City&nbsp; &nbsp; &nbsp; &nbsp; |\n| Los Angeles&nbsp; | 3792621&nbsp; &nbsp; &nbsp; | Really Big City |\n| Manhattan&nbsp; &nbsp; | 1619090&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| New York &nbsp; &nbsp; | 8175133&nbsp; &nbsp; &nbsp; | Really Big City |\n| Philadelphia | 1526006&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| Phoenix&nbsp; &nbsp; &nbsp; | 1445632&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| Queens &nbsp; &nbsp; &nbsp; | 2272771&nbsp; &nbsp; &nbsp; | Big City&nbsp; &nbsp; &nbsp; &nbsp; |\n| San Antonio&nbsp; | 1327407&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| San Diego&nbsp; &nbsp; | 1307402&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n| The Bronx&nbsp; &nbsp; | 1408473&nbsp; &nbsp; &nbsp; | Small City&nbsp; &nbsp; &nbsp; |\n+--------------+--------------+-----------------+<\/pre>\n\n\n\n<p>This example uses a searched <code>CASE<\/code> expression to evaluate the results from the <code>LatestRecordedPopulation<\/code> column of the <code>Application.Cities<\/code> table. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Data Types<\/h2>\n\n\n\n<p>In SQL Server, the data type of the input expression and the <code>WHEN<\/code> expressions must be the same or must be an implicit conversion. <\/p>\n\n\n\n<p>Here&#8217;s what happens if they aren&#8217;t:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @stock_ticker varchar(4) = 'V';\n\nSELECT Company =  \n      CASE @stock_ticker  \n         WHEN 1 THEN 'Apple'\n         WHEN 2 THEN 'Facebook'\n         WHEN 3 THEN 'Mastercard'\n         WHEN 4 THEN 'Visa'\n         ELSE 'Not in the portfolio'  \n      END<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Msg 245, Level 16, State 1, Line 3\nConversion failed when converting the varchar value 'V' to data type int.<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Order of Evaluation<\/h2>\n\n\n\n<p>The T-SQL <code>CASE<\/code> expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. <\/p>\n\n\n\n<p>To demonstrate this, let&#8217;s use multiple <code>WHEN<\/code> expressions that share the same value:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @stock_ticker varchar(4) = 'V';\n\nSELECT Company =  \n    CASE @stock_ticker  \n        WHEN 'V' THEN 'Visa 1'\n        WHEN 'V' THEN 'Visa 2'\n        WHEN 'V' THEN 'Visa 3'\n        ELSE 'Not in the portfolio'  \n    END<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------+\n| Company &nbsp; |\n|-----------|\n| Visa 1&nbsp; &nbsp; |\n+-----------+<\/pre>\n\n\n\n<p>In this case, it stopped at the first <code>WHEN<\/code> expression.<\/p>\n\n\n\n<p>There may be the occasional scenario where an expression is evaluated before a <code>CASE<\/code> expression receives the results of the expression as its input. In such scenarios, you could end up with an error. This could happen if you include an aggregate expression as the <code>WHEN<\/code> expression.<\/p>\n\n\n\n<p>For this reason, Microsoft <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/case-transact-sql?view=sql-server-ver15#remarks\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/case-transact-sql?view=sql-server-ver15#remarks\">advises<\/a> that:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.<\/p><\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">ELSE is Optional<\/h2>\n\n\n\n<p>The <code>ELSE<\/code> argument is optional. Therefore, we could rewrite our &#8220;affordability&#8221; example as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @price int = 1500;\n\nSELECT Affordability =  \n    CASE   \n        WHEN @price &lt; 100 THEN 'Cheap'\n        WHEN @price >= 100 AND @price &lt; 500 THEN 'Affordable'\n        WHEN @price >= 500 THEN 'Expensive'\n    END    <\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------+\n| Affordability &nbsp; |\n|-----------------|\n| Expensive &nbsp; &nbsp; &nbsp; |\n+-----------------+<\/pre>\n\n\n\n<p>Although, be mindful that you could end up with <code>NULL<\/code> if you omit the <code>ELSE<\/code> argument. <\/p>\n\n\n\n<p>The following example results in <code>NULL<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @price int = 1500;\n\nSELECT Affordability =  \n    CASE   \n        WHEN @price &lt; 100 THEN 'Cheap'\n        WHEN @price >= 100 AND @price &lt; 500 THEN 'Affordable'\n        WHEN @price >= 500 AND @price &lt; 1000 THEN 'Expensive'\n    END    <\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------+\n| Affordability &nbsp; |\n|-----------------|\n| NULL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |\n+-----------------+<\/pre>\n\n\n\n<p>In such cases, we could always add an <code>ELSE<\/code> argument, just in case (sorry about the pun!):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @price int = 1500;\n\nSELECT Affordability =  \n    CASE   \n        WHEN @price &lt; 100 THEN 'Cheap'\n        WHEN @price >= 100 AND @price &lt; 500 THEN 'Affordable'\n        WHEN @price >= 500 AND @price &lt; 1000 THEN 'Expensive'\n        ELSE 'Unknown'\n    END  <\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------+\n| Affordability &nbsp; |\n|-----------------|\n| Unknown &nbsp; &nbsp; &nbsp; &nbsp; |\n+-----------------+<\/pre>\n\n\n\n<p>Admittedly, this example is probably a bit contrived. After all, there&#8217;s no need to cap &#8220;expensive&#8221;. If something is expensive at under $1000, then it&#8217;s also expensive if it&#8217;s over $1000.<\/p>\n\n\n\n<p>But the point is, you can use <code>ELSE<\/code> to catch anything that isn&#8217;t covered by the <code>WHEN<\/code> expression\/s.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Nested CASE Expressions<\/h2>\n\n\n\n<p>You can nest <code>CASE<\/code> expressions if required.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @price int, @on_sale bit;\nSET @price = 1500;\nSET @on_sale = 1;\n\nSELECT Affordability =  \n    CASE   \n        WHEN @price &lt; 100 THEN 'Cheap'\n        WHEN @price >= 100 THEN \n            CASE @on_sale\n                WHEN 0 THEN 'Expensive (but it''s not currently on sale)' \n                WHEN 1 THEN 'Expensive (and it''s already on sale!)'\n            END\n    END<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------------------------------------+\n| Affordability &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |\n|---------------------------------------|\n| Expensive (and it's already on sale!) |\n+---------------------------------------+<\/pre>\n\n\n\n<p>However, it&#8217;s important to note that only 10 levels of nesting is allowed for <code>CASE<\/code> expressions in SQL Server. If you try to nest more than 10 levels, then you&#8217;ll get an error.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">CASE in an ORDER BY Clause<\/h2>\n\n\n\n<p>As mentioned, the T-SQL <code>CASE<\/code> expression can be used in any statement or clause that allows a valid expression. Therefore, you can use it in statements such as <code>SELECT<\/code>, <code>UPDATE<\/code>, <code>DELETE<\/code> and <code>SET<\/code>, and in clauses such as <code>IN<\/code>, <code>WHERE<\/code>, <code>ORDER BY<\/code>, <code>GROUP BY<\/code>, and <code>HAVING<\/code>.<\/p>\n\n\n\n<p>Using a <code>CASE<\/code> expression in a statement&#8217;s <code>ORDER BY<\/code> clause can be handy when you want to make a special exception for certain values when ordering your results. <\/p>\n\n\n\n<p>Suppose we run the following query against a table containing music genres.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT Genre \nFROM MusicGenres\nORDER BY Genre ASC;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------+\n| Genre &nbsp; |\n|---------|\n| Blues &nbsp; |\n| Country |\n| Hip Hop |\n| Jazz&nbsp; &nbsp; |\n| Metal &nbsp; |\n| Other &nbsp; |\n| Pop &nbsp; &nbsp; |\n| Rap &nbsp; &nbsp; |\n| Rock&nbsp; &nbsp; |\n+---------+<\/pre>\n\n\n\n<p>Here, we order the results by the <code>Genre<\/code> column, in ascending order. <\/p>\n\n\n\n<p>This is fine except for one thing. The genre called <strong>Other<\/strong>. Wouldn&#8217;t it be nice if we could move <strong>Other<\/strong> to the bottom?<\/p>\n\n\n\n<p>We can achieve this with the <code>CASE<\/code> expression by taking the above query, and modifying it as follows.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT Genre\nFROM MusicGenres\nORDER BY \n    CASE Genre\n        WHEN 'Other' THEN 1\n        ELSE 0\n    END\n    ASC, Genre ASC;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------+\n| Genre &nbsp; |\n|---------|\n| Blues &nbsp; |\n| Country |\n| Hip Hop |\n| Jazz&nbsp; &nbsp; |\n| Metal &nbsp; |\n| Pop &nbsp; &nbsp; |\n| Rap &nbsp; &nbsp; |\n| Rock&nbsp; &nbsp; |\n| Other &nbsp; |\n+---------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">CASE in an UPDATE Statement<\/h2>\n\n\n\n<p>Here&#8217;s an example of using a <code>CASE<\/code> expression in an <code>UPDATE<\/code> statement.<\/p>\n\n\n\n<p>Suppose we have the following table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------+-----------+-----------+----------+\n| DogId &nbsp; | DogName &nbsp; | GoodDog &nbsp; | Dinner &nbsp; |\n|---------+-----------+-----------+----------|\n| 1 &nbsp; &nbsp; &nbsp; | Fetch &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n| 2 &nbsp; &nbsp; &nbsp; | Fluffy&nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n| 3 &nbsp; &nbsp; &nbsp; | Wag &nbsp; &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n| 1001&nbsp; &nbsp; | Brian &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n| 1002&nbsp; &nbsp; | Rambo &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n| 1003&nbsp; &nbsp; | BamBam&nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | NULL &nbsp; &nbsp; |\n+---------+-----------+-----------+----------+<\/pre>\n\n\n\n<p>We have recently added the <code>Dinner<\/code> column, and it&#8217;s still <code>NULL<\/code>, waiting for values to be inserted.<\/p>\n\n\n\n<p>But the values to be inserted will depend on the value of the <code>GoodDog<\/code> column. <\/p>\n\n\n\n<p>We could use a <code>CASE<\/code> expression in such a scenario.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE Dogs \nSET Dinner = \n    CASE GoodDog\n        WHEN 1 THEN 'Sunday Roast'\n        ELSE 'Airline food'\n    END\n\nSELECT * FROM Dogs;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------+-----------+-----------+--------------+\n| DogId &nbsp; | DogName &nbsp; | GoodDog &nbsp; | Dinner &nbsp; &nbsp; &nbsp; |\n|---------+-----------+-----------+--------------|\n| 1 &nbsp; &nbsp; &nbsp; | Fetch &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n| 2 &nbsp; &nbsp; &nbsp; | Fluffy&nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 3 &nbsp; &nbsp; &nbsp; | Wag &nbsp; &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 1001&nbsp; &nbsp; | Brian &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n| 1002&nbsp; &nbsp; | Rambo &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 1003&nbsp; &nbsp; | BamBam&nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n+---------+-----------+-----------+--------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">CASE in an INSERT Statement<\/h2>\n\n\n\n<p>We can take the table from the above example, and insert a new value.<\/p>\n\n\n\n<p>And we can again take advantage of the <code>CASE<\/code> expression to insert the appropriate value into the <code>Dinner<\/code> column.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @DogName nvarchar(60), @GoodDog bit;\nSET @DogName = 'Lazy';\nSET @GoodDog = 0;\n\nINSERT INTO Dogs ( DogName, GoodDog, Dinner )\nVALUES (\n    @DogName,\n    @GoodDog,\n    CASE @GoodDog\n        WHEN 1 THEN 'Sunday Roast'\n        ELSE 'Airline food'\n    END\n    );\n\nSELECT * FROM Dogs;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------+-----------+-----------+--------------+\n| DogId &nbsp; | DogName &nbsp; | GoodDog &nbsp; | Dinner &nbsp; &nbsp; &nbsp; |\n|---------+-----------+-----------+--------------|\n| 1 &nbsp; &nbsp; &nbsp; | Fetch &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n| 2 &nbsp; &nbsp; &nbsp; | Fluffy&nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 3 &nbsp; &nbsp; &nbsp; | Wag &nbsp; &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 1001&nbsp; &nbsp; | Brian &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n| 1002&nbsp; &nbsp; | Rambo &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n| 1003&nbsp; &nbsp; | BamBam&nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | Sunday Roast |\n| 1004&nbsp; &nbsp; | Lazy&nbsp; &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; | Airline food |\n+---------+-----------+-----------+--------------+<\/pre>\n\n\n\n<p>This time the <code>CASE<\/code> expression was evaluating the value of a variable we&#8217;d just set, then inserting the appropriate value into the <code>Dinner<\/code> column.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Is it a CASE Statement or CASE Expression?<\/h2>\n\n\n\n<p>In SQL, lots of things are referred to as a &#8220;statement&#8221; when in fact, they&#8217;re something else. This also appears to be true for the T-SQL &#8220;<code>CASE<\/code> statement&#8221;.<\/p>\n\n\n\n<p>Although it&#8217;s often referred to as the <code>CASE<\/code> statement, it&#8217;s more accurate to call it the <code>CASE<\/code> <em>expression<\/em>. This is also how the Microsoft documentation refers to it. <\/p>\n\n\n\n<p>In SQL Server, rather than being a statement itself, <code>CASE<\/code> can be used in any statement or clause that allows a valid expression. An expression is a combination of symbols and operators that are evaluated to obtain a single data value.<\/p>\n\n\n\n<p>However, some <a href=\"https:\/\/database.guide\/what-is-a-dbms\/\" title=\"What is a DBMS?\">DBMS<\/a>s distinguish between the <code>CASE<\/code> statement, and the <code>CASE<\/code> expression, and have a slightly different syntax for each. <a href=\"https:\/\/database.guide\/what-is-mysql\/\" title=\"What is MySQL?\">MySQL<\/a> distinguishes between the <code>CASE<\/code> statement and the <code>CASE<\/code> operator, which is essentially the same as the <code>CASE<\/code> expression.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server, the T-SQL CASE expression is a scalar expression that returns a value based on conditional logic. It evaluates a list of conditions and returns a value, based on the outcome of those conditions.. In some ways, the SQL Server CASE expression is similar to IF&#8230;ELSE. However, CASE allows you to check for &#8230; <a title=\"SQL Server CASE Expression\" class=\"read-more\" href=\"https:\/\/database.guide\/sql-server-case-expression\/\" aria-label=\"Read more about SQL Server CASE Expression\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[77,48,61,20],"class_list":["post-11151","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-mssql","tag-sql","tag-t-sql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11151","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=11151"}],"version-history":[{"count":31,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11151\/revisions"}],"predecessor-version":[{"id":11261,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11151\/revisions\/11261"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=11151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=11151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=11151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}