[thelist] TIP: Other SQL Case Statement Usages.....

Anthony Baratta anthony at baratta.com
Fri Oct 27 10:37:45 CDT 2006


<tip author="Anthony Baratta" type="MS SQL, Case Statement">
If you know SQL, you are probably familiar with the CASE statement and how you can use it with the SELECT statement to modify the output the date returned from the database. e.g.

CASE
WHEN isNull(ONHAND_QTY,0) > isNull(BUFFER_QTY,0) THEN 'High'
WHEN isNull(ONHAND_QTY,0) < isNull(BUFFER_QTY,0) AND isNull(ONHAND_QTY,0) > 0 THEN 'Low'
ELSE 'None'
END as STOCK_STATUS

But did you know that the CASE statement is also usefull in the WHERE and ORDER BY clauses?? e.g.

WHERE STATUS_CD = 'RETIRED'
AND CASE
WHEN @SEARCH_COLUMN = 'FooCode' THEN FOO_CD
WHEN @SEARCH_COLUMN = 'BarCode' THEN BAR_CD
ELSE FOOBAR_NM
END LIKE @SEARCH_TEXT

ORDER BY CASE
WHEN @SEARCH_COLUMN = 'FooCode' THEN FOO_CD
WHEN @SEARCH_COLUMN = 'BarCode' THEN BAR_CD
ELSE FOOBAR_NM
END 

This can save having to create the SQL string manually and then executing it.
</tip>



More information about the thelist mailing list