1.5 — Views, Functions, Stored Procedures, Triggers and Error Handling
DP-800 Domain 1 · Skill 1.5
What You Need to Know
Programmability objects package logic in the database, close to the data, reusable, and securable with GRANT EXECUTE or GRANT SELECT. DP-800 also expects robust error handling.
Choosing the object
| Object | Returns | Use it for | Watch out for |
|---|---|---|---|
| View | A virtual table | Hiding joins and columns; a stable interface; security through GRANT SELECT on the view |
No parameters. Indexed views materialize data but come with restrictions |
| Scalar function | One value | Reusable expressions (a tax calculation) | Can be slow row by row. Keep them inlineable (scalar UDF inlining) |
| Inline table-valued function | A table, from one SELECT |
A parameterized view; the RLS predicate shape | Prefer it over multi-statement TVFs for performance |
| Multi-statement TVF | A table variable built in steps | Complex procedural results | Poor cardinality estimates |
| Stored procedure | Result sets and output parameters | Business operations, multi-step writes, APIs (Data API builder can expose them) | Use parameters (never concatenate SQL); SET NOCOUNT ON |
| Trigger (AFTER or INSTEAD OF; DML or DDL) | — | Enforcing cross-row rules, audit trails, reacting to changes | Hidden logic, extra latency inside the transaction. Never call slow external services from a trigger |
Error handling
CREATE OR ALTER PROCEDURE dbo.TransferStock @From int, @To int, @Qty int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- any runtime error rolls back the whole transaction
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Stock SET Qty = Qty - @Qty WHERE WarehouseID = @From;
IF @@ROWCOUNT = 0 THROW 50001, 'Source warehouse not found.', 1;
UPDATE dbo.Stock SET Qty = Qty + @Qty WHERE WarehouseID = @To;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
INSERT dbo.ErrorLog (ProcName, ErrorNumber, ErrorText, LoggedAt)
VALUES (ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_MESSAGE(), SYSUTCDATETIME());
THROW; -- re-raise the original error to the caller
END CATCH
END;
| Element | Purpose |
|---|---|
TRY...CATCH |
Catches errors of severity above 10 that don't end the connection |
ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE() |
Details, available inside CATCH |
THROW |
Raises an error (custom numbers are 50000 and up), or re-raises the caught one when used without arguments. Preferred over RAISERROR in new code |
XACT_ABORT ON |
Makes runtime errors roll back the entire transaction, so no partial work remains |
@@TRANCOUNT / XACT_STATE() |
Check whether a transaction is open, or doomed (-1), before you commit or roll back |
Transient errors and retries
Deadlock victims (error 1205) and transient cloud errors should be retried, either in the application (with retry policies in the drivers) or in a T-SQL retry loop. Don't retry errors that come from your own logic.
Key Concept: A view is a stable interface, an inline TVF is a parameterized view, a procedure is an operation, and a trigger is a reaction inside the transaction. Wrap writes in TRY...CATCH with XACT_ABORT and THROW.
Exam Traps
- Multi-statement TVFs or non-inlineable scalar UDFs in hot queries
- Calling an external REST endpoint from a trigger (it slows or fails the user's transaction)
- Catching an error and silently swallowing it (always
THROWor log it and re-raise) - Committing in CATCH without checking
XACT_STATE()
Check your understanding
Practice Scenario
A stored procedure updates three tables. If any statement fails, nothing must be left half done, the failure must be logged, and the caller must still receive the original error. Which pattern should you use?
Log in to save your progress.
Sources
- CREATE VIEW, Microsoft Learn
- Create user-defined functions, Microsoft Learn
- Scalar UDF inlining, Microsoft Learn
- TRY...CATCH (Transact-SQL), Microsoft Learn
- THROW (Transact-SQL), Microsoft Learn