case statement in SQL, how to return multiple variables?
The basic way, unfortunately, is to repeat yourself.
SELECT
CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END,
CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END
FROM
<table>
Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It's just redundant typing.
In MS SQL Server (2005+) you could possible use CROSS APPLY as an alternative to this. Though I have no idea how performant it is...
SELECT
*
FROM
<table>
CROSS APPLY
(
SELECT a1, b1 WHERE <condition 1>
UNION ALL
SELECT a2, b2 WHERE <condition 2>
UNION ALL
SELECT a3, b3 WHERE <condition 3>
)
AS case_proxy
The noticable downside here is that there is no ELSE equivalent and as all the conditions could all return values, they need to be framed such that only one can ever be true at a time.
EDIT
If Yuck's answer is changed to a UNION rather than JOIN approach, it becomes very similar to this. The main difference, however, being that this only scans the input data set once, rather than once per condition (100 times in your case).
EDIT
I've also noticed that you may mean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames values in value1 and value2. This could be formed like this...
WITH
checked_data AS
(
SELECT
CASE WHEN <condition1> THEN 1
WHEN <condition2> THEN 2
WHEN <condition3> THEN 3
...
ELSE 100
END AS condition_id,
*
FROM
<table>
)
,
results (condition_id, value1, value2) AS
(
SELECT 1, a1, b1
UNION ALL
SELECT 2, a2, b2
UNION ALL
SELECT 3, a3, b3
UNION ALL
...
SELECT 100, a100, b100
)
SELECT
*
FROM
checked_data
INNER JOIN
results
ON results.condition_id = checked_data.condition_id
A CASE
statement can return only one value.
You may be able to turn this into a subquery and then JOIN
it to whatever other relations you're working with. For example (using SQL Server 2K5+ CTEs):
WITH C1 AS (
SELECT a1 AS value1, b1 AS value2
FROM table
WHERE condition1
), C2 AS (
SELECT a2 AS value1, b2 AS value2
FROM table
WHERE condition2
), C3 AS (
SELECT a3 AS value1, b3 AS value2
FROM table
WHERE condition3
)
SELECT value1, value2
FROM -- some table, joining C1, C2, C3 CTEs to get the cased values
;