Launch Deal · 50% off 1st month ·

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 THROW or 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?

Report issue

Log in to save your progress.

Sources