CASE, COALESCE, NULLIF, TRY_CONVERT, String/Date Functions, and Error-Tolerant Parsing

Learning outcomes

ServiceHub receives CSV rows from a legacy scheduling vendor. Dates arrive in several textual formats, blank strings stand in for missing values, amount fields contain invalid text, and a report uses CASE to “protect” a division by zero but still fails. Robust ingestion is not achieved by catching every exception after the fact; it starts with understanding expression type/evaluation rules and creating an explicit reject path for values that cannot be safely converted.

1. CASE returns an expression; it is not general control flow

CASE chooses one result expression from conditions. SQL Server determines one output data type from the result expressions using precedence rules. That means mixed branches can trigger conversion errors even when the branch that appears “selected” looks safe. Also, Microsoft documents that some expressions—especially aggregates used in WHEN conditions—can be evaluated before CASE receives their values, so do not depend on CASE as a universal short-circuit shield.

SELECT    CASE WHEN 1 = 1 THEN CAST(10 AS decimal(10,2)) ELSE 0 END AS typed_result;GODECLARE @numerator decimal(12,2) = 100.00,        @denominator decimal(12,2) = 0.00;SELECT @numerator / NULLIF(@denominator, 0) AS safe_ratio;GO

NULLIF(@denominator,0) returns NULL when the denominator is zero, and division by NULL yields NULL instead of a divide-by-zero error. Whether NULL is the correct business result is a separate decision; the expression only makes the arithmetic boundary explicit.

2. COALESCE chooses the first non-NULL expression—but type/evaluation still matter

COALESCE(a,b,...) is standardized syntax for selecting the first non-NULL expression. SQL Server rewrites it conceptually as a CASE expression, so result type follows precedence across its inputs. Microsoft also warns that expressions such as subqueries can be evaluated more than once. Do not embed volatile/expensive subqueries in COALESCE and assume single evaluation.

USE ServiceHubLab;GOSELECT    work_order_id,    COALESCE(NULLIF(LTRIM(RTRIM(description)), N''), N'[no description]') AS display_descriptionFROM ops.WorkOrderORDER BY work_order_id;GO

NULLIF(trimmed_text,N'') converts an empty-after-trim value into NULL; COALESCE then supplies presentation text. Do not persist '[no description]' as a replacement for NULL unless that literal is truly a business value.

3. TRY_CONVERT is for expected conversion failures, not impossible conversions

TRY_CONVERT returns the converted value when successful and NULL for many ordinary conversion failures. But Microsoft documents that explicitly disallowed conversions still raise an error. The function is best used at ingestion boundaries where “some source rows may be malformed” is expected and the application needs to classify them.

SELECT    TRY_CONVERT(decimal(12,2), '125.75') AS valid_amount,    TRY_CONVERT(decimal(12,2), 'not-money') AS invalid_amount,    TRY_CONVERT(datetime2(0), '2026-08-22T13:30:00', 126) AS iso_timestamp;GO-- TRY_CONVERT still errors for an explicitly prohibited conversion such as int -> xml.-- SELECT TRY_CONVERT(xml, 4);GO

For dates, prefer unambiguous representations such as ISO 8601 and explicit style codes where relevant. Parsing user-localized dates like 08/09/26 without a locale contract creates ambiguity before SQL Server ever sees the value.

4. Determinism affects computed/indexed expressions

A deterministic function returns the same result for the same inputs and database state; a nondeterministic function can vary. Time functions such as GETDATE() are nondeterministic. This matters when SQL Server requires deterministic expressions for indexed views/computed-column indexing and when you reason about repeatable transformations.

DECLARE @observed_at datetime2(3) = SYSUTCDATETIME();SELECT @observed_at AS first_use,       @observed_at AS second_use;GO

Capturing the value once also makes application/business semantics explicit: “one event timestamp for this operation.” It does not magically make SYSUTCDATETIME() a deterministic function for schema/indexing rules.

5. Build a staging table that preserves raw text and typed results

Instead of directly inserting dirty CSV text into ops.WorkOrder, stage raw values, derive typed candidates, and classify failures. This pattern preserves evidence and lets business validation remain separate from parsing validation.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab03') IS NULL EXEC(N'CREATE SCHEMA lab03 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab03.ImportRow;GOCREATE TABLE lab03.ImportRow(    import_row_id int IDENTITY PRIMARY KEY,    work_order_id_text nvarchar(50) NULL,    amount_text nvarchar(50) NULL,    scheduled_text nvarchar(100) NULL,    note_text nvarchar(400) NULL);GOINSERT lab03.ImportRow(work_order_id_text, amount_text, scheduled_text, note_text)VALUES(N'1001', N'125.75', N'2026-08-22T13:30:00', N'  ready  '),(N'bad-id', N'44.10', N'2026-08-22T14:00:00', N''),(N'1003', N'not-money', N'31/12/2026 09:00', N'legacy locale date'),(NULL, NULL, NULL, NULL);GO

USE ServiceHubLab;GOWITH parsed AS(    SELECT        import_row_id,        work_order_id_text,        amount_text,        scheduled_text,        TRY_CONVERT(bigint, NULLIF(LTRIM(RTRIM(work_order_id_text)), N'')) AS work_order_id,        TRY_CONVERT(decimal(12,2), NULLIF(LTRIM(RTRIM(amount_text)), N'')) AS amount,        TRY_CONVERT(datetime2(0), NULLIF(LTRIM(RTRIM(scheduled_text)), N''), 126) AS scheduled_at,        NULLIF(LTRIM(RTRIM(note_text)), N'') AS normalized_note    FROM lab03.ImportRow)SELECT    *,    CASE      WHEN work_order_id_text IS NOT NULL AND work_order_id IS NULL THEN 'invalid work_order_id'      WHEN amount_text IS NOT NULL AND amount IS NULL THEN 'invalid amount'      WHEN scheduled_text IS NOT NULL AND scheduled_at IS NULL THEN 'invalid ISO timestamp'      ELSE 'accepted-or-missing'    END AS parse_statusFROM parsedORDER BY import_row_id;GO

The CTE avoids repeating conversions inside multiple CASE branches. Production ingestion may persist typed/rejected rows separately, but this lab keeps the raw evidence visible.

6. Deliberately wrong approach: COALESCE every parse failure to zero

A developer writes COALESCE(TRY_CONVERT(decimal(12,2), amount_text),0). Malformed 'not-money' and a genuinely missing amount both become zero. The pipeline appears “robust” because it never throws, but it has silently corrupted meaning.

7. Hands-on lab: accepted rows and rejection evidence

USE ServiceHubLab;GOWITH p AS(    SELECT *,      TRY_CONVERT(bigint, work_order_id_text) AS work_order_id,      TRY_CONVERT(decimal(12,2), amount_text) AS amount,      TRY_CONVERT(datetime2(0), scheduled_text, 126) AS scheduled_at    FROM lab03.ImportRow)SELECT import_row_id, work_order_id, amount, scheduled_atFROM pWHERE (work_order_id_text IS NULL OR work_order_id IS NOT NULL)  AND (amount_text IS NULL OR amount IS NOT NULL)  AND (scheduled_text IS NULL OR scheduled_at IS NOT NULL)ORDER BY import_row_id;GOWITH p AS(    SELECT *,      TRY_CONVERT(bigint, work_order_id_text) AS work_order_id,      TRY_CONVERT(decimal(12,2), amount_text) AS amount,      TRY_CONVERT(datetime2(0), scheduled_text, 126) AS scheduled_at    FROM lab03.ImportRow)SELECT import_row_id, work_order_id_text, amount_text, scheduled_textFROM pWHERE (work_order_id_text IS NOT NULL AND work_order_id IS NULL)   OR (amount_text IS NOT NULL AND amount IS NULL)   OR (scheduled_text IS NOT NULL AND scheduled_at IS NULL)ORDER BY import_row_id;GO

Verification checklist

• You can identify which inputs failed parsing without losing their raw text.
• You do not substitute zero/empty string for every NULL automatically.
• You use an unambiguous timestamp contract for accepted data.
• You can explain why TRY_CONVERT may still error for explicitly disallowed conversions.
• You can explain why CASE is not a general guaranteed short-circuit shield.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab03.ImportRow;GO

8. Production judgment and next bridge

Expression robustness comes from explicit contracts: which strings mean missing, which formats are accepted, what type/precision results are required, what happens on invalid data, and whether repeated/nondeterministic evaluation is acceptable. Keep raw source data long enough to diagnose transformations; make quarantine/rejection an observable path.

Lesson 5 moves this contract across the network boundary. Even a perfect SQL schema can be undermined when an application driver infers a parameter as nvarchar(4000), sends the wrong decimal scale, loses an offset, or confuses application null with SQL NULL.

Authoritative references

• CASE — result typing and documented evaluation caveats
• COALESCE — CASE rewrite, type precedence and possible repeated evaluation
• NULLIF — equal-value-to-NULL semantics
• TRY_CONVERT — NULL-on-failure and prohibited-conversion behavior
• Deterministic and nondeterministic functions — schema/indexing determinism rules
• GETDATE — nondeterministic current-time behavior