Expression Language Patterns - SQL Server Integration Services Design Patterns - page 286

not, for example, attempt to cast a text value to a number and pro-
grammatically handle any type cast errors in the same expression.
No comments allowed: The fact that there is no provision for code
comments can be a significant downside when you are using lengthy
or complex expressions. Any comments documenting the purpose of
the expression have to be done peripherally—for example, on the data
flow or control flow surface as an SSIS annotation.
• Complex statements can be difficult: Simple assignments or
comparisons are easy to do and are usually easy to understand after
the fact. However, introducing even a moderate amount of com-
plexity to an expression can make for a lengthy and convoluted
statement. Consider the case of a multiconditional If statement. In
most other dialects, one could simply perform an If/Then/Else If
operation to account for more than one test condition. However, the
expression language doesn't have such behavior, so to build such
logic you need to nest conditional operators. Listing 10-1 shows
how you might easily address four possible conditions in a CASE
operation in Transact-SQL. By contrast, Listing 10-2 shows a sim-
ilar example using the expression language (note that I manually
wrapped the text to fit it on the page). Although the result of the
operation is the same, the latter has conditional operators nested
two levels deep and is more difficult to develop and maintain.
Listing 10-1 . Multiconditional Evaluation in T-SQL
SELECT CASE WHEN @TestCase = 3 THEN 'Test case = Solid'
WHEN @TestCase = 2 THEN 'Test case = Liquid'
WHEN @TestCase = 1 THEN 'Test case = Gas'
ELSE 'Unknown test case' END [TestCaseType]
Listing 10-2 . Multiconditional Evaluation in the Expression Language
(TestCase == 1) ? "Test case = Gas" : (TestCase == 2 ?
"Test case = Liquid" : (TestCase == 3 ? "Test case
= Solid" : "Unknown Test Case"))