Reusable functions and views
Reusing business logic keeps systems consistent and reduces functional legacy. In Thinkwise projects, repetition usually shows up in views, tasks, validations, and reports.
When to extract
Section titled “When to extract”Extract logic when:
- The same calculation/rule appears in more than one place.
- A “label” or “status” is derived from codes/flags.
- Multiple views need the same base joins or filters.
- A task repeats the same query logic used by screens or reports.
Choose the right building block
Section titled “Choose the right building block”| You need… | Prefer | Notes |
|---|---|---|
| A single value (status, total, label) | Scalar function (prefer inlineable) | Keep it simple and deterministic. |
| A reusable query (rows/columns) | Inline table-valued function (TVF) | Composable in views; good for shared base queries. |
| Side effects (insert/update/send) | Procedure | Keep writes explicit and reviewable. |
| A stable “read model” | View (often built on TVFs) | Keep views thin; delegate complex logic. |
Pattern: smallest unit first
Section titled “Pattern: smallest unit first”Start with the smallest reusable unit (row-level rule, domain check, formatting), then compose bigger queries from those units.
select
i.invoice_id
, dbo.fn_invoice_total(i.invoice_id) as total
, dbo.fn_invoice_status(i.invoice_id) as status
from invoice iUse the same functions from views, tasks, validations, and reports instead of rewriting expressions.
Performance notes
Section titled “Performance notes”- Prefer inline TVFs for shared base queries; they behave more like composable building blocks.
- Be careful with scalar functions in filters (
where) on large sets; validate with an execution plan. - Test in context (the actual view/report) and compare IO/CPU when changing shared functions.
Common pitfalls
Section titled “Common pitfalls”- Creating “mega functions” that mix unrelated rules; prefer smaller composable functions.
- Encoding business rules in UI concepts; keep them reusable and explicit.
- Repeating joins across many views; centralize into a TVF or base view.