T-SQL Stored Procedures for SQL Server: Params, Return Codes, sp_executesql & TRY/CATCH

A sql stored procedure in SQL Server is the single most over-rated and under-rated object in the engine at the same time. New backends call them "obsolete" because their ORM emits parameterised statements; senior engineers call them indispensable because the same engine still ships gnarly migrations, multi-row writes, batched ETL jobs, and security-boundary RPCs through dbo.usp_* procedures running inside a SQL Agent job. The truth is that every modern SQL Server deployment in 2026 — Azure SQL DB, on-prem 2022, the Synapse dedicated pool — still pays the bills on top of a layer of T-SQL stored procedures.

This guide is the cheat sheet you wished existed the first time you stared at a 600-line legacy CREATE PROCEDURE. It walks through every primitive a senior T-SQL engineer ships: the CREATE PROCEDURE anatomy that hides three result channels in plain sight, the four parameter modes (input, output, default, table-valued) you need to know, sp_executesql versus EXEC(@sql) for dynamic SQL without an injection trap, and the four-step TRY/CATCH + XACT_ABORT contract that lets you log, rollback, and re-raise without swallowing errors. Every section pairs a teaching block with a Solution-Tail interview answer — a stored procedure example sql sample, a step-by-step trace, an output table, and a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the subqueries practice library →, rehearse on Microsoft SQL interview sets →, and stack the error-handling muscles with exception handling drills →.

---

On this page

• Why stored procedures still win in 2026
• Anatomy of a real T-SQL stored procedure
• Parameter types — input, output, default, table-valued
• sp_executesql vs EXEC — dynamic SQL safely
• TRY/CATCH + transactions — production-grade error handling
• Cheat sheet — T-SQL sproc recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why stored procedures still win in 2026

A T-SQL stored procedure is a server-side function with four superpowers that ORMs and parameterised statements cannot replicate cheaply

The mental model in one line: a SQL Server stored procedure is the only object that encapsulates multi-statement logic, caches its plan across callers, defines a security boundary, and collapses a chatty client into a single network round-trip — all four at once. Drop any one of those and you can write the same code as a dbt model or an EF Core query; need all four and you reach for CREATE PROCEDURE.

The four jobs a sproc actually does well.

• Encapsulation. A sproc bundles many statements (INSERT + UPDATE + SELECT + control flow) behind one named entry point. Callers stop knowing the schema; they know the API. The internal layout can change without touching .NET code.
• Plan reuse. SQL Server compiles a sproc into a query plan on first execution and caches it. Subsequent calls with different parameters reuse the plan — no parse, no bind, no compile cost.
• Security boundary. You can GRANT EXECUTE ON dbo.usp_OrderCreate TO app_user without granting any table-level rights. The app touches one named entry point; the underlying tables stay locked down behind ownership chaining.
• Network round-trip reducer. A sproc that runs three statements is one server round-trip. Three parameterised statements from the app would be three round-trips — meaningful in a hot path that runs thousands of times per second.

When ORMs and parameterised statements are enough.

• Single-row reads / single-row writes with no business logic on the server. A parameterised SELECT ... WHERE id = @id or INSERT ... VALUES (@a, @b) from EF Core / Dapper / a thin DAL is cleaner than a one-statement sproc.
• Generated SQL from dbt, Hibernate, SQLAlchemy. The framework knows the schema; sprocs add a layer of indirection that breaks discoverability.
• Read-heavy reporting routed through a view or a materialised table. The query optimiser handles plan caching of the parameterised SELECT just fine.

When the sproc is the right hammer.

• Multi-statement writes inside a transaction. Insert into Orders, insert into OrderLines, update Inventory, all on one server round-trip with one BEGIN TRAN boundary.
• Server-side branching logic. "If the user is in the EU and the order is over 500 EUR, also write to EuAuditLog." Two callbacks to the app is a non-starter; the sproc keeps it local.
• Security RPC. The app has EXEC permission on a sproc and no permission on the underlying tables. The sproc is the only door.
• Performance-critical batch. Five round-trips collapse to one. Network latency goes away.

Sproc vs function vs view — the right hammer for each nail.

Object	Returns	Side-effects	Use it for
Stored procedure	Result sets, OUTPUT params, RETURN code	Yes (INSERT / UPDATE / DELETE / DDL)	Multi-statement business logic, transactional writes, security RPC
Scalar UDF	A single scalar value	No (read-only by definition)	Formatting / computed columns / WHERE predicate helpers
Inline TVF	A single SELECT result set	No	A parameterised view; inlined into the calling query plan
Multi-statement TVF	A populated table variable	No	Logic that needs intermediate steps but must compose like a table
View	A query, materialised at use time	No	Named SELECT contracts; security via column-level grants

The "sprocs are unmaintainable" myth.

The classic complaint — "sprocs are buried in the database, nobody can find them, nobody can diff them, nobody tests them" — was true in 2005 when sprocs lived as binary objects in SQL Server's catalog and nowhere else. In 2026, every sane shop ships sprocs as .sql files in git, deploys them with a migration tool (SqlPackage, DbUp, Flyway, dbatools), runs them through unit tests with tSQLt, and reviews them in PR exactly like any other code. The myth dies the moment your repo has a db/procs/usp_OrderCreate.sql file.

Who still hires for T-SQL sprocs in 2026.

• Microsoft shops. Every team with a meaningful .NET footprint runs SQL Server, and most still ship transactional writes through dbo.usp_* procedures.
• Azure SQL teams. Azure SQL DB / Managed Instance / Synapse all support full T-SQL sprocs; the cloud lift-and-shift wave brought decades of legacy procs into Azure.
• .NET monoliths and modernisers. Even teams migrating to microservices keep critical writes inside sprocs until they can extract the underlying domain.
• BI loaders. SSIS / ADF Mapping Data Flows / Synapse pipelines still call stored procedures for slowly-changing dimensions, fact-table merges, and audit logging.
• ISVs that ship SQL Server schemas. Vendors that hand a customer a .bacpac ship sprocs as the API surface.

What interviewers listen for.

• Do you say "the sproc is a security boundary" — not just a "named query"? — senior signal.
• Do you reach for SET NOCOUNT ON and SET XACT_ABORT ON as part of the skeleton? — required answer.
• Do you mention plan cache when discussing why sprocs are faster than ad-hoc SQL? — required answer.
• Do you list three result channels (result sets, OUTPUT params, RETURN code) instead of conflating them into "the result"? — senior signal.

Worked example — encapsulation vs ORM round-trips

Detailed explanation. A hot-path .NET service places orders. The naive ORM flow is "INSERT into Orders, get back the new id, INSERT into OrderLines, get back the line ids, UPDATE Inventory" — three round-trips minimum. The same logic as a single sproc is one round-trip and one transaction boundary.

Question. Compare the wire-level traffic of placing an order via three EF Core calls vs one EXEC dbo.usp_OrderCreate call. How many round-trips and how many transactions does each take? Where does the sproc save effort?

Input. A single order: customer_id = 42, total = 199.00, two line items.

Code.

-- Sproc version — one round-trip, one transaction
CREATE PROCEDURE dbo.usp_OrderCreate
    @customer_id int,
    @total       decimal(12,2),
    @lines       dbo.OrderLineRows READONLY,
    @order_id    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.Orders(customer_id, total)
        VALUES(@customer_id, @total);

        SET @order_id = SCOPE_IDENTITY();

        INSERT INTO dbo.OrderLines(order_id, sku, qty, price)
        SELECT @order_id, sku, qty, price FROM @lines;

        UPDATE i
           SET i.stock = i.stock - l.qty
          FROM dbo.Inventory i
          JOIN @lines l ON l.sku = i.sku;

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Step-by-step explanation.

1. The sproc receives the entire order (header + lines + caller context) in a single EXEC call. One network round-trip.
2. Inside the sproc, three statements run under one BEGIN TRAN boundary. If any statement fails, XACT_ABORT ON and the CATCH block roll the whole order back atomically.
3. The OUTPUT parameter @order_id returns the surrogate key to the app without a separate SELECT SCOPE_IDENTITY() round-trip.
4. The ORM equivalent would be three separate parameterised commands, three round-trips, and either three implicit transactions or an explicit one wrapped around all of them — which still costs three round-trips.

Output.

Approach	Round-trips	Transactions	Code	Plan
3 × ORM calls (no explicit TX)	3	3 implicit	3 SQL strings	3 cached
3 × ORM calls (1 explicit TX)	3 + BEGIN/COMMIT	1 explicit	3 SQL strings	3 cached
1 × EXEC usp_OrderCreate	1	1 explicit	1 sproc name	1 sproc plan

Rule of thumb. If a write path includes "INSERT, then read back the id, then INSERT something that depends on that id, then UPDATE a third table" — that is the sproc-shaped silhouette. The savings (round-trips, transaction boundary, plan reuse) compound at hot-path throughput.

Worked example — when not to use a sproc

Detailed explanation. A reporting team has 12 simple SELECT * lookups, one per page. Wrapping each in a sproc adds indirection — dbo.usp_GetUserById is functionally the same as a parameterised SELECT * FROM Users WHERE id = @id. The sproc layer adds nothing and forces every schema change to ripple through both the table and the sproc.

Question. A junior wants to wrap a single-row select inside a sproc "for safety." Why is this anti-pattern, and what's the modern alternative?

Input. A single-row read: GetUserById(@id).

Code.

-- Anti-pattern — sproc adds zero value
CREATE PROCEDURE dbo.usp_GetUserById
    @id int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, name, email FROM dbo.Users WHERE id = @id;
END;

-- Better — parameterised statement from the app, no sproc layer
-- (EF Core / Dapper / sqlcmd / sp_executesql from app code)
SELECT id, name, email FROM dbo.Users WHERE id = @id;

Step-by-step explanation.

1. The sproc holds one statement. There is no transaction boundary to manage, no branching logic, no OUTPUT parameter, no audit log.
2. Schema changes (adding a phone column) now ripple through both the table DDL and the sproc's SELECT list. The sproc is friction, not abstraction.
3. The parameterised statement is still plan-cached by SQL Server (auto-parameterisation or sp_executesql-style binding), so the "plan reuse" argument does not buy anything here.
4. The right sproc layer for single-row reads is no sproc layer. Reserve sprocs for paths where multiple statements, branching logic, OUTPUT params, or a security RPC actually buys something.

Output.

Aspect	One-statement sproc	Parameterised statement
Schema-change ripple	Two files	One file
Plan cache	Yes	Yes (auto-param)
Transaction boundary	None needed	None needed
Discoverability	Hidden in DB	Visible in code
Security boundary	Slight	Slight (table grant)

Rule of thumb. Build the sproc once you have one of: multi-statement transactional logic, OUTPUT parameters, branching server-side logic, a security RPC, or a round-trip hot path. Without any of those, prefer parameterised SQL.

SQL interview question on sproc-shaped silhouettes

A senior interviewer often opens with: "Walk me through which writes in your service should be a stored procedure and which should be an ORM call. What invariants do you protect by picking the sproc?" It tests whether you understand the four jobs (encapsulation, plan reuse, security, round-trip reduction) or whether you mechanically pick one tool.

Solution Using the four-job test

-- Decision rubric:
--   1. Does the write touch >= 2 tables in one logical unit? → sproc.
--   2. Does it need server-side branching / loops? → sproc.
--   3. Is the path hot (>1000 calls/sec)? → sproc to collapse round-trips.
--   4. Does the app have NO table-level rights? → sproc as security RPC.
-- Anything else → parameterised statement.

-- Example — qualifies on rules 1, 2, and 3:
CREATE PROCEDURE dbo.usp_TransferFunds
    @from_account int,
    @to_account   int,
    @amount       decimal(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        UPDATE dbo.Accounts SET balance = balance - @amount
         WHERE account_id = @from_account
           AND balance >= @amount;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK;
            THROW 50001, 'insufficient funds', 1;
        END

        UPDATE dbo.Accounts SET balance = balance + @amount
         WHERE account_id = @to_account;

        INSERT INTO dbo.TransferLog(from_id, to_id, amount)
        VALUES(@from_account, @to_account, @amount);

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Step-by-step trace.

Step	What happens	Why the sproc earns its keep
1	Caller invokes EXEC dbo.usp_TransferFunds 1, 2, 100	One round-trip carries three SQL operations
2	Sproc subtracts amount from source, checks @@ROWCOUNT	Server-side branching — no app callback needed
3	If zero rows updated, THROW raises insufficient-funds	One transaction boundary, one rollback decision
4	Otherwise add to destination, write audit row	Multi-table write under one TX boundary
5	COMMIT, RETURN 0	Caller reads RC, knows success / fail in one EXEC

The four-job test fires on encapsulation (three writes, one entry point), plan reuse (every transfer reuses the same compiled plan), and round-trip reduction (one wire call replaces what would be three or four).

Output:

return_code	semantics
0	success — funds moved + audit row written
(THROW raised)	insufficient funds — rollback fired, caller catches

Why this works — concept by concept:

• Multi-statement encapsulation — three logical operations (debit, credit, audit) collapse to one named entry point. The caller doesn't know the schema; they know the API.
• SET NOCOUNT ON — suppresses the per-statement rows affected chatter that would otherwise add network noise and confuse strict ADO.NET clients.
• SET XACT_ABORT ON — promotes most runtime errors to "abort the batch and rollback" semantics, so a stray constraint violation cannot leave the transaction half-applied.
• Server-side branching — IF @@ROWCOUNT = 0 is the kind of check that, done in the app, would require a second round-trip. Inside the sproc it's free.
• One transaction boundary — BEGIN TRAN / COMMIT / ROLLBACK wrap the whole logical operation. ACID per business unit, not per statement.
• RETURN 0 as success — the integer return code is a cheap, dialect-standard "did it work?" signal that any caller can read.
• Cost — one round-trip per transfer; one cached plan; one transaction. The sproc trades a small amount of compile-time complexity for substantial run-time savings.

SQL
Topic — subqueries
Subqueries & sproc-style logic (SQL)

Practice →

SQL
Company — Microsoft
Microsoft SQL interview problems

Practice →

---

2. Anatomy of a real T-SQL stored procedure

Every senior-grade T-SQL stored procedure has the same skeleton — SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY ... END TRY BEGIN CATCH THROW; END CATCH;

The mental model in one line: CREATE PROCEDURE is a header (schema + name + parameter list) plus a body that runs in three result channels — SELECT rows, OUTPUT parameters, and an integer RETURN code — all of which the caller can read on the same EXEC line. Once you can name the three channels and the four preamble lines, you can build any procedure from memory.

The CREATE PROCEDURE skeleton.

• CREATE OR ALTER PROCEDURE — the idempotent form (SQL Server 2016 SP1+). Re-runs cleanly in deploy pipelines without IF OBJECT_ID(...) DROP ceremony.
• Schema-qualified name. dbo.usp_OrderCreate — always two-part. Unqualified names resolve to the caller's default schema and break in security contexts. The usp_ prefix marks "user stored procedure" by convention.
• Parameter list. Comma-separated, each declared as @name type [= default] [OUTPUT] [READONLY]. No parentheses required; parentheses around the list are optional.
• AS BEGIN ... END — the body. BEGIN/END is technically optional but always written, both for readability and because some authoring tools require it.
• RETURN n — the integer status code. Zero by convention means success; non-zero means error.

The four-line preamble every sproc needs.

SET NOCOUNT ON;            -- 1. suppress "x rows affected" chatter
SET XACT_ABORT ON;         -- 2. force ROLLBACK on most runtime errors
SET ANSI_NULLS ON;         -- 3. NULL = NULL is NULL (already default in modern SQL Server)
SET QUOTED_IDENTIFIER ON;  -- 4. double quotes wrap identifiers, single quotes wrap strings

In practice the last two are session-level defaults today; the first two are the ones you actually type. SET NOCOUNT ON is mandatory for any sproc called by ADO.NET / Dapper / EF — without it, the rowcount messages confuse ExecuteScalar / ExecuteNonQuery consumers. SET XACT_ABORT ON is the cheapest insurance against partial commits in the entire engine.

The three result channels.

• Result sets. Every SELECT (without INTO) in the body emits a result set the caller can iterate. A sproc can emit multiple result sets — common for stored "report" procs.
• OUTPUT parameters. Scalars returned by reference. Declared with the OUTPUT keyword on both the sproc header and the caller's EXEC line. Useful for "new surrogate key" or "computed total" without a result set.
• RETURN code. A single 32-bit integer. By convention 0 = ok, non-zero = error. Cheap and universal; every ADO.NET caller can read it.

Caller binds three channels in one EXEC.

DECLARE @new_id int, @rc int;

EXEC @rc = dbo.usp_OrderCreate
    @customer_id = 42,
    @total       = 199.00,
    @order_id    = @new_id OUTPUT;

SELECT @rc AS return_code, @new_id AS order_id;

The single EXEC carries every input, binds the OUTPUT parameter back, captures the RETURN code in @rc, and any SELECT inside the body would still emit as a result set on the same wire payload.

Common skeleton mistakes.

• Forgetting SET NOCOUNT ON. The caller's ExecuteScalar ends up reading the rowcount message as a "result," and you spend an afternoon debugging "why is my int 1?"
• Skipping SET XACT_ABORT ON. A constraint violation in the middle of the sproc leaves a half-applied transaction without rollback. Production data drifts.
• Using EXEC @rc = sproc; IF @rc <> 0 RAISERROR(...) — fine, but THROW (since 2012) is the modern idiom. Prefer it.
• Putting a comma after the last parameter. T-SQL does not tolerate trailing commas in parameter lists. The error message ("Incorrect syntax near 'AS'") is misleading.
• RETURN 0 without RETURN. Implicit "no RETURN" still returns 0, but explicit RETURN 0 documents intent and lets a code-reviewer grep for early-exit paths.

Worked example — the full skeleton: usp_OrderCreate

Detailed explanation. A canonical order-placement procedure carries one input header, one OUTPUT key, and a RETURN status. It demonstrates every part of the anatomy: schema-qualified name, typed inputs, OUTPUT, the four-line preamble, transaction scaffolding, TRY/CATCH, and an explicit RETURN 0.

Question. Write dbo.usp_OrderCreate that takes @customer_id and @total, inserts an Orders row, returns the new surrogate key in an OUTPUT parameter, and returns 0 on success. Wrap everything in TRY/CATCH with XACT_ABORT.

Input. Inserts one row into Orders.

customer_id	total
42	199.00

Code.

CREATE OR ALTER PROCEDURE dbo.usp_OrderCreate
    @customer_id int,
    @total       decimal(12,2),
    @order_id    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.Orders(customer_id, total)
        VALUES(@customer_id, @total);

        SET @order_id = SCOPE_IDENTITY();

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Step-by-step explanation.

1. CREATE OR ALTER PROCEDURE is idempotent — re-runs in deploys without dropping. Pre-2016 you'd need IF OBJECT_ID('dbo.usp_OrderCreate') IS NOT NULL DROP PROCEDURE ...; CREATE PROCEDURE ....
2. The parameter list has two inputs (@customer_id, @total) and one OUTPUT (@order_id). Order in the list does not have to match caller binding order.
3. SET NOCOUNT ON suppresses the (1 row affected) chatter. SET XACT_ABORT ON promotes most runtime errors to "rollback the whole batch."
4. BEGIN TRAN opens an explicit transaction. The INSERT runs; if it succeeds, SCOPE_IDENTITY() returns the new identity value generated in this scope — safe even if a trigger inserts into another identity column.
5. SET @order_id = SCOPE_IDENTITY() writes the new key into the OUTPUT parameter slot. The caller sees it after the EXEC returns.
6. COMMIT makes the row durable. RETURN 0 signals success.
7. If anything threw, BEGIN CATCH fires. XACT_STATE() returns 1 (committable), 0 (no transaction), or -1 (doomed). <> 0 means "we have a transaction to roll back."
8. THROW; (no args) re-raises the original error to the caller, preserving error number, message, severity, and line — the modern preferred re-raise.

Output. The sproc returns nothing visible (no SELECT). The caller reads:

Channel	Caller code	Value
OUTPUT	@new_id after EXEC	the new surrogate key (e.g., 1024)
RETURN	@rc = EXEC @rc = ...	0 on success
Result set	(none — no SELECT in body)	n/a

Rule of thumb. Every transactional write sproc looks like this: four-line preamble, BEGIN TRY / BEGIN TRAN, the work, COMMIT, RETURN 0, then BEGIN CATCH with XACT_STATE()-aware rollback and a bare THROW. Copy it as your starter template; tweak only the middle.

Worked example — multiple result sets in one sproc

Detailed explanation. A sproc can emit multiple result sets — the caller reads them in order with NextResult() on a SqlDataReader. This is convenient for "report" procs that return a summary table and a detail table from one EXEC.

Question. Write dbo.usp_OrderSummary that takes a @customer_id and returns two result sets: a summary row (total orders, total spend) and the line-by-line orders list. Show how the caller iterates.

Input. A customer with three orders totalling 350.

order_id	customer_id	total
1	42	100
2	42	150
3	42	100

Code.

CREATE OR ALTER PROCEDURE dbo.usp_OrderSummary
    @customer_id int
AS
BEGIN
    SET NOCOUNT ON;

    -- Result set 1 — summary
    SELECT
        @customer_id AS customer_id,
        COUNT(*)      AS total_orders,
        SUM(total)    AS total_spend
    FROM dbo.Orders
    WHERE customer_id = @customer_id;

    -- Result set 2 — detail
    SELECT order_id, total, order_date
    FROM dbo.Orders
    WHERE customer_id = @customer_id
    ORDER BY order_date DESC;

    RETURN 0;
END;

Step-by-step explanation.

1. The first SELECT produces a single-row summary. SQL Server treats every non-INTO SELECT as a result set on the wire.
2. The second SELECT produces a multi-row detail set. Both are sent back on the same EXEC call.
3. The .NET caller opens a SqlDataReader, reads the summary row, calls reader.NextResult() to advance to the detail, then iterates.
4. Multiple result sets save round-trips when a UI panel needs both "headline number" and "underlying rows" in one shot.

Output.

Result set	Contents
1 — summary	customer_id=42, total_orders=3, total_spend=350
2 — detail	three rows ordered by order_date DESC

Rule of thumb. Use multiple result sets for genuine batch-the-round-trip scenarios. Avoid them for "general purpose" procs where the caller might or might not need the second set — undocumented optional result sets confuse clients.

SQL interview question on the three result channels

The probe is usually: "I see your sproc has a RETURN, an OUTPUT @id, and a SELECT *. What does each channel give back to the caller, and when should I pick one over the others?"

Solution Using the three-channel mental model

-- One sproc that demonstrates all three channels
CREATE OR ALTER PROCEDURE dbo.usp_ThreeChannels
    @customer_id int,
    @new_order_id int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.Orders(customer_id, total) VALUES(@customer_id, 0);
        SET @new_order_id = SCOPE_IDENTITY();

        -- Result set: caller can iterate
        SELECT order_id, customer_id, total
        FROM dbo.Orders
        WHERE customer_id = @customer_id;

        COMMIT;
        RETURN 0;   -- success
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        RETURN 1;   -- generic error
    END CATCH;
END;

-- Caller binds all three:
DECLARE @rc int, @new_id int;
EXEC @rc = dbo.usp_ThreeChannels
    @customer_id  = 42,
    @new_order_id = @new_id OUTPUT;
SELECT @rc AS rc, @new_id AS new_id;

Step-by-step trace.

Channel	What it carries	When to pick it
Result set (SELECT)	Tabular rows — any shape	Variable-cardinality data: list of orders, top-N, ad-hoc report
OUTPUT param (@x OUTPUT)	Scalar(s) by reference, typed	Fixed scalars: new surrogate key, computed total, "rows updated"
RETURN code	One 32-bit integer	Coarse status: 0 = ok, 1 = generic error, 2 = "not found", etc.

The interview answer is all three serve different purposes. Picking the wrong channel costs round-trips, plan-cache pressure, or wire-protocol confusion.

Output:

channel	sample value	caller code
RETURN	0	EXEC @rc = ...
OUTPUT	1024	@new_id after EXEC
Result set	rows	SqlDataReader / IDataReader

Why this works — concept by concept:

• Channel separation — each channel has a different semantic. Result sets are iterable; OUTPUT params are typed scalars; RETURN is a status. Conflating them (e.g., returning the new id as the RETURN code) breaks contract: RETURN is int only and reserved for status, not data.
• SCOPE_IDENTITY() over @@IDENTITY — SCOPE_IDENTITY() returns the last identity generated in this scope. @@IDENTITY returns the last identity generated in any scope, including triggers — which is a classic bug if a trigger inserts into a log table with its own identity column.
• OUTPUT requires both header and call site — the caller must repeat OUTPUT on the EXEC line. Omitting it silently passes by value, and the sproc's change to the parameter is lost.
• RETURN is int only — you cannot RETURN a string or a decimal. If you need typed data back, use OUTPUT.
• Multiple result sets allowed — the body can emit any number of result sets. The .NET reader advances with NextResult().
• Cost — all three channels share the same network round-trip; using all three at once is cheaper than calling three single-purpose sprocs.

SQL
Topic — design
Sproc design & API problems (SQL)

Practice →

SQL
Topic — implementation
Server-side implementation problems

Practice →

---

3. Parameter types — input, output, default, table-valued

T-SQL has four parameter modes — input, output, default, table-valued — and no IN/OUT keyword; the OUTPUT keyword does both directions

The mental model in one line: every T-SQL parameter is "input" by default, becomes "output" with the OUTPUT keyword on both the header and the call site, becomes "default-supplied" by giving it a = value in the header, and becomes "table-valued" by declaring its type as a READONLY user-defined table type. Once you can quote that one sentence, the entire parameter system clicks into place.

The four parameter modes in one matrix.

Mode	Signature	Caller binds with	Use it for
Input	@id int	EXEC usp_X @id = 7	Required scalar inputs (the common case)
Output	@out int OUTPUT	EXEC usp_X @out = @v OUTPUT	Return scalar(s) without a SELECT
Default	@status varchar(20) = 'active'	EXEC usp_X (omit) — uses default	Backwards-compatible overloads
Table-valued (TVP)	@rows dbo.OrderRows READONLY	EXEC usp_X @rows = @tbl	Batched inserts; multi-row in one round-trip

Input parameters in detail.

• Typed. Every parameter has an explicit T-SQL type (int, varchar(50), decimal(12,2), datetime2(3)). Mistyping is the #1 silent bug — varchar defaults to length 1 in some contexts.
• NULL handling. Parameters can accept NULL unless you explicitly check. The pattern IF @x IS NULL THROW 50001, '...', 1 is your friend.
• Parameter sniffing. The optimiser compiles a plan based on the first parameter values it sees. Subsequent calls with very different selectivity reuse that plan and may run badly — the classic "fast for some users, slow for others" bug.
• OPTION (RECOMPILE) — force a fresh plan every call. Solves sniffing at the cost of compile overhead per execution.

Output parameters in detail.

• Header. @out int OUTPUT — the OUTPUT keyword goes after the type.
• Body. SET @out = <expr> writes the value. Last write wins.
• Caller. Must declare a local variable, pass it in the EXEC with = @local OUTPUT. Forgetting OUTPUT on the call site silently discards the result.
• Multiple OUTPUTs. Every parameter can be OUTPUT. Use for "return two scalars" without a single-row SELECT.

Default parameters in detail.

• Header. @status varchar(20) = 'active' — the = expression is the default.
• Call site. Omit the parameter entirely (positional or named) — the default fires.
• NULL default. @id int = NULL is the idiomatic "optional parameter" marker. Inside the body, branch on IF @id IS NULL.
• Backwards compatibility. Add a new parameter with a default; existing callers continue to work without changes.

Table-valued parameters (TVP) in detail.

• Step 1 — user-defined table type. CREATE TYPE dbo.OrderRows AS TABLE (sku varchar(20), qty int, price decimal(12,2));
• Step 2 — sproc declares it READONLY. @rows dbo.OrderRows READONLY. Every TVP must be READONLY — you can SELECT from it but not INSERT / UPDATE / DELETE.
• Step 3 — caller passes a SqlDbType.Structured DataTable (.NET) or builds a local table variable in T-SQL.
• Use case. Insert N rows in one round-trip without OPENROWSET, BULK INSERT, or N separate INSERTs. A 1000-row TVP runs in roughly the time of one INSERT plus the row-copy cost.

The "no IN/OUT keyword" T-SQL convention.

Oracle PL/SQL has IN, OUT, IN OUT. T-SQL has no IN keyword — every parameter is implicitly input, and OUTPUT toggles "also output" (i.e., bidirectional). If you read a T-SQL sproc and the caller modifies a parameter without OUTPUT, the change is invisible to the caller. This trips Oracle engineers daily.

WITH RESULT SETS — explicit result-set shape.

EXEC dbo.usp_Report
WITH RESULT SETS (
    (customer_id int, total_orders int, total_spend decimal(12,2))
);

Lets the caller assert column names + types — useful when the sproc body changes the column list and the caller needs a contract test. Particularly handy in dynamic SQL where the column list is computed.

Worked example — input + output parameters together

Detailed explanation. A typical "create order" sproc takes a header as input and returns the new surrogate key as output. The caller binds both in one EXEC line.

Question. Write dbo.usp_CreateOrder with two inputs (@customer_id, @total) and one output (@order_id). Show the EXEC call that captures the new id.

Input.

customer_id	total
42	199.00

Code.

CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder
    @customer_id int,
    @total       decimal(12,2),
    @order_id    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Orders(customer_id, total)
    VALUES(@customer_id, @total);

    SET @order_id = SCOPE_IDENTITY();
    RETURN 0;
END;

-- Call site
DECLARE @new_id int;
EXEC dbo.usp_CreateOrder
    @customer_id = 42,
    @total       = 199.00,
    @order_id    = @new_id OUTPUT;

SELECT @new_id AS new_order_id;

Step-by-step explanation.

1. The sproc declares three parameters. The third has OUTPUT after its type — this declares the slot as bidirectional.
2. Inside the body, SET @order_id = SCOPE_IDENTITY() writes the just-generated identity into the OUTPUT slot.
3. The caller declares a local @new_id. The EXEC line passes @order_id = @new_id OUTPUT — the OUTPUT keyword on the call site is required; omitting it silently discards the return.
4. After the EXEC, @new_id holds whatever the sproc wrote into @order_id.

Output.

@new_id	RETURN code
1024 (the new identity)	0

Rule of thumb. Every OUTPUT parameter needs the OUTPUT keyword in two places — the header and every call site. Forgetting it on the call site is the silent "I always get NULL" bug.

Worked example — default parameters for backwards-compatible overloads

Detailed explanation. A usp_OrderSearch proc originally took @customer_id only. A new requirement adds an optional @from_date filter. You can either fork into usp_OrderSearchByDate or add a defaulted parameter — the second keeps existing callers working without changes.

Question. Add a @from_date parameter to usp_OrderSearch with a NULL default. Inside the body, branch on IF @from_date IS NULL to skip the date filter when not supplied.

Input. Two callers — one supplies @from_date, the other doesn't.

Code.

CREATE OR ALTER PROCEDURE dbo.usp_OrderSearch
    @customer_id int,
    @from_date   datetime2(3) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT order_id, total, order_date
    FROM dbo.Orders
    WHERE customer_id = @customer_id
      AND (@from_date IS NULL OR order_date >= @from_date)
    ORDER BY order_date DESC;
END;

-- Old caller — works unchanged
EXEC dbo.usp_OrderSearch @customer_id = 42;

-- New caller — supplies the date
EXEC dbo.usp_OrderSearch @customer_id = 42, @from_date = '2026-01-01';

Step-by-step explanation.

1. The new @from_date parameter has a NULL default. Existing callers that don't supply it continue to work — they get the implicit NULL.
2. The WHERE clause uses the "optional predicate" pattern: (@from_date IS NULL OR order_date >= @from_date). The first arm short-circuits when no date is supplied; the second arm filters when one is.
3. The new caller supplies @from_date = '2026-01-01'. The first arm is FALSE, so the second arm fires and filters orders to that date forward.
4. Parameter sniffing watch-out. Optional predicates often create plans that work well only for the "supplied" or only for the "not supplied" case. Add OPTION (RECOMPILE) if you see plan instability.

Output.

Old caller	New caller
All orders for customer 42	Orders for customer 42 since 2026-01-01

Rule of thumb. Every new parameter you add to a stable sproc should have a default. The default preserves backwards compatibility — existing callers, integration tests, and ETL jobs keep running without a coordinated deploy.

Worked example — table-valued parameter for batched inserts

Detailed explanation. Insert 100 order lines in one round-trip without BULK INSERT ceremony. Define a user-defined table type, pass the rows as a TVP, and INSERT ... SELECT from it inside the sproc.

Question. Define dbo.OrderLineRows as a TVP type and write usp_OrderLineInsert(@order_id, @rows) that inserts every row of @rows into OrderLines in one statement.

Input. @order_id = 1024 plus three lines.

sku	qty	price
sku-1	2	9.99
sku-2	1	49.50
sku-3	5	3.00

Code.

-- Step 1 — define the user-defined table type
CREATE TYPE dbo.OrderLineRows AS TABLE
(
    sku   varchar(50)    NOT NULL,
    qty   int            NOT NULL,
    price decimal(12,2)  NOT NULL
);

-- Step 2 — the sproc accepts the TVP as READONLY
CREATE OR ALTER PROCEDURE dbo.usp_OrderLineInsert
    @order_id int,
    @rows     dbo.OrderLineRows READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.OrderLines(order_id, sku, qty, price)
    SELECT @order_id, sku, qty, price
    FROM @rows;

    RETURN 0;
END;

-- Step 3 — caller (T-SQL example; .NET uses SqlDbType.Structured)
DECLARE @tbl dbo.OrderLineRows;
INSERT INTO @tbl(sku, qty, price) VALUES
    ('sku-1', 2,  9.99),
    ('sku-2', 1, 49.50),
    ('sku-3', 5,  3.00);

EXEC dbo.usp_OrderLineInsert @order_id = 1024, @rows = @tbl;

Step-by-step explanation.

1. CREATE TYPE dbo.OrderLineRows AS TABLE declares a user-defined table type. Every TVP requires a UDT type definition before the sproc can reference it.
2. The sproc declares @rows dbo.OrderLineRows READONLY. The READONLY keyword is mandatory for TVPs — you can SELECT from @rows but you cannot INSERT, UPDATE, or DELETE into it inside the sproc.
3. The body runs a single INSERT ... SELECT that fans out every row of the TVP into OrderLines. One statement, N rows, one round-trip from the caller.
4. The .NET caller would pass a DataTable configured with SqlDbType.Structured and TypeName = "dbo.OrderLineRows". The T-SQL caller above uses a local table variable.

Output. Three new OrderLines rows attached to order_id = 1024.

Approach	Round-trips for 100 lines	Total wire cost
100 × parameterised INSERT	100	high
100 × per-row sproc EXEC	100	high
1 × sproc EXEC with TVP	1	low — single batch

Rule of thumb. Whenever a write path inserts more than ~5 rows of the same shape, a TVP beats per-row INSERTs on round-trip count and plan cache pressure. Define the UDT once; reuse it across every sproc that takes the same row shape.

SQL interview question on return-multiple-values

A senior interviewer often opens with: "I need to return both 'the new id' and 'how many rows were touched' from a single sproc call. Walk me through every way T-SQL gives you to return multiple values, and the trade-offs."

Solution Using OUTPUT parameters for both scalars

CREATE OR ALTER PROCEDURE dbo.usp_OrderArchive
    @cutoff_date  datetime2(3),
    @rows_archived int OUTPUT,
    @last_id       int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.OrdersArchive(order_id, customer_id, total, order_date)
        OUTPUT inserted.order_id
        SELECT order_id, customer_id, total, order_date
        FROM dbo.Orders
        WHERE order_date < @cutoff_date;

        SET @rows_archived = @@ROWCOUNT;

        SELECT @last_id = MAX(order_id)
        FROM dbo.OrdersArchive;

        DELETE FROM dbo.Orders
        WHERE order_date < @cutoff_date;

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

-- Call site
DECLARE @n int, @id int;
EXEC dbo.usp_OrderArchive
    @cutoff_date  = '2025-01-01',
    @rows_archived = @n  OUTPUT,
    @last_id       = @id OUTPUT;
SELECT @n AS archived, @id AS last_id;

Step-by-step trace.

Step	What happens	Channel
1	Caller passes cutoff date	Input param
2	Sproc INSERTs matching rows into archive	(work)
3	@@ROWCOUNT captured into @rows_archived	OUTPUT slot written
4	MAX(order_id) captured into @last_id	OUTPUT slot written
5	Original rows deleted, COMMIT, RETURN 0	RETURN channel
6	Caller reads @n and @id	OUTPUT bound at call site

Two OUTPUT params return two distinct scalars in one round-trip. The RETURN code is reserved for coarse status (0 = ok); the per-row data lives in OUTPUT.

Output:

return_code	rows_archived	last_id
0	17	4090

Why this works — concept by concept:

• Multiple OUTPUT params — T-SQL allows any number of OUTPUT slots. Each is a typed scalar return channel.
• RETURN is reserved for status — the single-int return code stays as "did it work?", not "what did it return?".
• @@ROWCOUNT captures last-statement effect — but it resets on every subsequent statement, so capture immediately into a variable.
• OUTPUT keyword in both places — header @n int OUTPUT and call site = @n OUTPUT. Drop either one and you silently lose the value.
• OUTPUT clause vs OUTPUT parameter — the OUTPUT inserted.order_id clause inside INSERT is unrelated to OUTPUT parameters; it emits a result set of the affected rows. Two distinct concepts that share a keyword.
• Cost — one round-trip, two scalar returns, one int status, one optional result set. Cheaper than three separate sprocs.

SQL
Topic — validation
Parameter validation problems (SQL)

Practice →

SQL
Topic — subqueries
Multi-statement sproc patterns

Practice →

---

4. sp_executesql vs EXEC — dynamic SQL safely

sp_executesql is the only safe dynamic-SQL primitive in T-SQL — EXEC(@sql) is string-concat, which is SQL injection by another name

The mental model in one line: sp_executesql @sql, @params, @arg1 = @x, @arg2 = @y runs dynamic SQL with parameter binding (safe, plan-cache-friendly) while EXEC(@sql) runs a string verbatim (injection-prone, one plan per literal). The split is binary: every dynamic SQL with user-influenced data goes through sp_executesql; EXEC(@sql) is reserved for deployment-time DDL with zero user input.

The two forms side by side.

-- UNSAFE: string concat → SQL injection
DECLARE @sql nvarchar(max) =
    N'SELECT * FROM Orders WHERE customer_id = ' + CAST(@id AS nvarchar(20));
EXEC(@sql);

-- SAFE: parameter binding via sp_executesql
DECLARE @sql nvarchar(max) =
    N'SELECT * FROM Orders WHERE customer_id = @id';
EXEC sp_executesql
     @sql,
     N'@id int',
     @id = @id;

The injection scenario is mechanical: pass @id = '1; DROP TABLE Orders;--'. The unsafe form concatenates that string into the SQL text, parses it as "SELECT ... WHERE customer_id = 1; DROP TABLE Orders;--" and runs both statements. The safe form sends the literal value 1; DROP TABLE Orders;-- as a bound parameter to a query whose text is fixed — the database parses the query text exactly once, binds the parameter as data, and either rejects or accepts the value but never executes it as code.

The sp_executesql signature.

EXEC sp_executesql
     @stmt        = N'... query text with @params ...',
     @params      = N'@p1 int, @p2 varchar(20)',
     @p1          = @local_p1,
     @p2          = @local_p2;

• @stmt — nvarchar(max) query text. Always prefix with N (Unicode literal) because the parameter requires nvarchar.
• @params — comma-separated parameter declarations (with types), in nvarchar. This is what makes the query parameterised, not concatenated.
• Bindings — for each declared param, a @name = @value pair after @params. Can include OUTPUT params with the OUTPUT keyword.

Why sp_executesql preserves the plan cache.

• SQL Server caches plans keyed by the query text plus a small parameter signature. With sp_executesql, the text is fixed and the parameter list is the same shape every call — one cached plan handles every binding.
• EXEC(@sql) builds a fresh string per call ('...WHERE customer_id = 1', then '...WHERE customer_id = 2'). Every literal is a different text, so the plan cache fills with thousands of single-use plans — the classic "single-use plan storm" that hammers compile budget.
• Forced parameterisation (a database-level setting) helps the EXEC form somewhat, but sp_executesql remains the right tool.

When EXEC(@sql) is acceptable.

• Deployment-time DDL that you build server-side: EXEC('CREATE INDEX ...') from a release script.
• No user input in the constructed string. All inputs come from sys.objects, sys.columns, or other catalog views; nothing crosses from a user-supplied value.
• One-shot maintenance. Rebuild every index whose name matches a pattern; clean up orphaned partitions.

If any of those conditions fails — especially "no user input" — switch to sp_executesql with QUOTENAME for identifiers and parameter binding for values.

Identifiers are not parameterisable.

• Parameters can only bind values — literals that go where a literal would go in static SQL.
• Identifiers (table names, column names, ORDER BY column choice) cannot be bound as parameters. The query parser needs to know the identifier at parse time.
• The safe pattern is QUOTENAME(@col) for identifier escaping plus server-side whitelisting against sys.columns / sys.tables.

-- Dynamic ORDER BY column with a whitelist + QUOTENAME
DECLARE @col sysname = @user_supplied_col;

-- Whitelist: reject anything not in sys.columns
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Orders')
      AND name = @col
)
    THROW 50002, 'invalid sort column', 1;

DECLARE @sql nvarchar(max) =
    N'SELECT order_id, total FROM dbo.Orders ORDER BY ' + QUOTENAME(@col);
EXEC sp_executesql @sql;

QUOTENAME wraps the identifier in square brackets and escapes any embedded brackets — the canonical T-SQL identifier-escape function. The whitelist catches the case where the input passes QUOTENAME but is still not a real column (typos, attacks).

Common patterns where sp_executesql shines.

• Dynamic pivot. The set of pivot columns is computed at runtime; the rest of the query is parameter-bound.
• Multi-tenant table name. The customer schema differs but the query shape is identical — embed the schema as a QUOTENAME'd identifier.
• Optional WHERE predicates. Build the WHERE clause from supplied parameters; bind every value, not just embed it.
• Backfill scripts that loop over partitions, each call parameter-bound for the partition key.

Worked example — the EXEC(@sql) injection trap

Detailed explanation. A junior writes EXEC('SELECT * FROM Orders WHERE id = ' + @id). The @id is supplied from a web form. A penetration tester submits 1; DROP TABLE Orders;-- and the entire table disappears.

Question. Demonstrate the injection on the EXEC form, then rewrite it with sp_executesql and explain why the same payload is harmless under the safe form.

Input.

@id	Caller intent	Attacker intent
1	normal lookup	normal lookup
1; DROP TABLE Orders;--	n/a	inject DDL

Code.

-- UNSAFE — string concat
DECLARE @id  nvarchar(50) = '1; DROP TABLE Orders;--';
DECLARE @sql nvarchar(max) =
    N'SELECT * FROM Orders WHERE customer_id = ' + @id;
EXEC(@sql);     -- BOOM — runs the DROP

-- SAFE — sp_executesql with parameter binding
DECLARE @id_int int = TRY_CAST('1; DROP TABLE Orders;--' AS int);
-- @id_int is NULL because the cast fails, but even if it didn't,
-- sp_executesql binds @id_int as data, never as SQL.

DECLARE @sql_safe nvarchar(max) =
    N'SELECT * FROM Orders WHERE customer_id = @id';
EXEC sp_executesql
     @sql_safe,
     N'@id int',
     @id = @id_int;

Step-by-step explanation.

1. The unsafe form concatenates the attacker's string into the SQL text. The text becomes 'SELECT * FROM Orders WHERE customer_id = 1; DROP TABLE Orders;--'.
2. The parser sees two statements separated by ;. Both execute. The Orders table is dropped.
3. The safe form keeps the SQL text fixed: 'SELECT * FROM Orders WHERE customer_id = @id'. The attacker's payload becomes a value bound to @id.
4. Two layers of defence fire: (a) TRY_CAST converts to NULL because the payload is not a valid int, and (b) even if it were a valid value, sp_executesql would bind it as a parameter — never re-parse it as SQL.
5. The plan cache stays clean too. The unsafe form would cache a new plan for every literal; the safe form caches one plan.

Output.

Form	Result for payload 1; DROP TABLE Orders;--
EXEC(@sql)	Orders table dropped
sp_executesql with bound @id	Query returns no rows; no DDL fires

Rule of thumb. Every dynamic SQL touching user-influenced data uses sp_executesql. The EXEC(@sql) form is reserved for catalog-only, no-user-input contexts (deploy scripts, partition rotation, etc.). Code review should reject every EXEC(@variable) that does not have a comment justifying it.

Worked example — dynamic ORDER BY with QUOTENAME and a whitelist

Detailed explanation. The user picks a sort column from a dropdown. The column name cannot be bound as a parameter (identifiers aren't parameterisable), so you need two layers: QUOTENAME for escaping and a server-side whitelist for "is this even a column on this table?"

Question. Write a sproc that takes a column name and a direction, validates the column against sys.columns, and runs SELECT ... ORDER BY QUOTENAME(@col) ASC/DESC via sp_executesql.

Input.

@col	@dir	Validity
order_date	DESC	valid
total	ASC	valid
1; DROP TABLE Orders	DESC	rejected by whitelist

Code.

CREATE OR ALTER PROCEDURE dbo.usp_OrdersSorted
    @col sysname = N'order_date',
    @dir varchar(4) = 'DESC'
AS
BEGIN
    SET NOCOUNT ON;

    -- Whitelist: must be a real column of dbo.Orders
    IF NOT EXISTS (
        SELECT 1 FROM sys.columns
        WHERE object_id = OBJECT_ID('dbo.Orders')
          AND name = @col
    )
        THROW 50010, 'invalid sort column', 1;

    -- Whitelist direction
    IF @dir NOT IN ('ASC', 'DESC')
        THROW 50011, 'invalid sort direction', 1;

    DECLARE @sql nvarchar(max) =
        N'SELECT order_id, customer_id, total, order_date
            FROM dbo.Orders
           ORDER BY ' + QUOTENAME(@col) + N' ' + @dir;

    EXEC sp_executesql @sql;
END;

-- Call site
EXEC dbo.usp_OrdersSorted @col = N'total', @dir = 'DESC';

Step-by-step explanation.

1. The sproc first asks sys.columns: "is @col actually a column of dbo.Orders?" If not, THROW raises a custom error. No dynamic SQL runs.
2. The direction is whitelisted against a literal IN ('ASC', 'DESC') check. No string concat from user input.
3. QUOTENAME(@col) wraps the column name in [...] and escapes any embedded ]. Even if the whitelist somehow let through a malformed name, QUOTENAME defangs it.
4. The final query text is fully owned by trusted code: catalog-validated identifier, whitelisted direction, no user-bound values to escape. sp_executesql runs it.
5. Note. This is one of the rare cases where the dynamic SQL has no runtime parameters to bind. The safety comes entirely from the catalog whitelist and QUOTENAME — sp_executesql is still preferred over EXEC(@sql) for plan cache uniformity.

Output. Sorted result set.

Validation step	Effect
@col in sys.columns	passes / throws 50010
@dir in IN ('ASC','DESC')	passes / throws 50011
QUOTENAME(@col)	escapes brackets

Rule of thumb. Dynamic identifiers always need three layers: a whitelist against the catalog, QUOTENAME for escape, and sp_executesql for execution. Skip any one of the three and you have a near-miss vulnerability waiting to happen.

Worked example — dynamic pivot driven by data

Detailed explanation. A reporting sproc pivots monthly revenue across an unknown number of months. The list of months has to be computed first, embedded into the pivot column list, and then executed. The values inside each pivoted cell are bound as parameters; the column list is built with QUOTENAME.

Question. Write a sproc that pivots dbo.MonthlyRevenue(month_key, revenue) across an unknown set of month_key values, returning one column per month.

Input. MonthlyRevenue rows.

month_key	revenue
2026-01	1200
2026-02	1500
2026-03	1700

Code.

CREATE OR ALTER PROCEDURE dbo.usp_RevenuePivot
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1 — build the list of months, QUOTENAME-escaped
    DECLARE @cols nvarchar(max);
    SELECT @cols = STRING_AGG(QUOTENAME(month_key), ',')
    FROM (SELECT DISTINCT month_key FROM dbo.MonthlyRevenue) m;

    IF @cols IS NULL
        RETURN 0;   -- no data to pivot

    -- Step 2 — assemble the PIVOT query (text is owned, columns are escaped)
    DECLARE @sql nvarchar(max) =
        N'SELECT * FROM (
              SELECT month_key, revenue FROM dbo.MonthlyRevenue
          ) src
          PIVOT (SUM(revenue) FOR month_key IN (' + @cols + N')) p;';

    -- Step 3 — execute via sp_executesql for plan cache
    EXEC sp_executesql @sql;
END;

Step-by-step explanation.

1. The set of pivot columns is unknown until runtime — that's the whole point of dynamic pivot. STRING_AGG(QUOTENAME(month_key), ',') builds the column list and escapes every identifier.
2. The pivot query is assembled with the dynamically built column list embedded as identifiers in IN (...). Identifiers cannot be parameterised; QUOTENAME is the escape primitive that makes this safe.
3. sp_executesql runs the assembled query. Even with no bound parameters in this specific case, the function is preferred over EXEC(@sql) for plan cache uniformity and for the consistent "this is dynamic SQL, audit me" signal in code review.
4. Watch-out. Every distinct month creates a different SQL text — so this is the one case where the plan cache will fragment across months. Acceptable for reporting (low frequency); avoid for hot paths.

Output. One row per source row, pivoted into one column per month.

2026-01	2026-02	2026-03
1200	1500	1700

Rule of thumb. Dynamic pivot is the canonical "I really need dynamic SQL" use case. The column list is data, not code, but it must be embedded as identifiers — so QUOTENAME + sp_executesql is the only safe pattern.

SQL interview question on safe dynamic SQL

A senior interviewer often frames this: "I see EXEC(@sql) in code review. When would you flag it, when would you let it through, and what's your rewrite?" It tests whether you understand the trade-off — not whether you ban dynamic SQL outright.

Solution Using sp_executesql with bound parameters

-- BEFORE — unsafe EXEC(@sql)
CREATE OR ALTER PROCEDURE dbo.usp_FindOrders_OLD
    @customer_id int,
    @min_total   decimal(12,2)
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        N'SELECT * FROM dbo.Orders WHERE customer_id = '
        + CAST(@customer_id AS nvarchar(20))
        + N' AND total >= '
        + CAST(@min_total AS nvarchar(20));
    EXEC(@sql);
END;

-- AFTER — safe sp_executesql with parameter binding
CREATE OR ALTER PROCEDURE dbo.usp_FindOrders
    @customer_id int,
    @min_total   decimal(12,2)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max) =
        N'SELECT order_id, customer_id, total, order_date
            FROM dbo.Orders
           WHERE customer_id = @customer_id
             AND total       >= @min_total';

    EXEC sp_executesql
         @sql,
         N'@customer_id int, @min_total decimal(12,2)',
         @customer_id = @customer_id,
         @min_total   = @min_total;
END;

Step-by-step trace.

Step	OLD (EXEC(@sql))	NEW (sp_executesql)
Build text	Concatenate values into SQL string	Static text with @customer_id and @min_total placeholders
Inject test	@customer_id = 1 OR 1=1 → leaks every row	@customer_id bound as int → cast fails or returns no rows
Plan cache	One plan per unique literal pair	One plan total
Auditability	Hard — text changes per call	Easy — text is static

The rewrite changes only two lines (the concat is removed; the EXEC becomes sp_executesql with a @params list). The behavioural change is dramatic: no injection, single cached plan, audit-friendly.

Output:

Path	Injected payload result
OLD	leaks rows / runs DDL
NEW	bound as data, returns matching rows or none

Why this works — concept by concept:

• sp_executesql signature — three-part call: query text, parameter declarations, parameter bindings. The text is fixed; the values are bound.
• Plan cache friendliness — the text is identical across all calls, so SQL Server caches one plan and reuses it for every customer + min_total pair.
• Injection-safe by construction — bound parameters are sent as typed values over TDS, never spliced into the SQL text. Even malformed inputs are rejected at the bind step or returned as no-data.
• N-prefix on the literal — N'...' marks the string as nvarchar, which sp_executesql requires. Missing the N is the most common copy-paste bug.
• OUTPUT params work too — sp_executesql accepts OUTPUT params with @x = @local OUTPUT exactly like EXEC dbo.usp_X. Dynamic SQL doesn't lose any expressivity.
• Cost — one parse, one plan, one cached plan, N executions. Compared to EXEC(@sql), this is orders-of-magnitude cheaper on plan cache pressure for hot paths.

SQL
Topic — sql-generation
SQL generation & dynamic SQL problems

Practice →

SQL
Topic — defensive coding
Defensive coding problems (SQL)

Practice →

---

5. TRY/CATCH + transactions — production-grade error handling

The four-step T-SQL error contract — capture context, decide rollback, log, re-raise — and SET XACT_ABORT ON makes the rollback decision for you

The mental model in one line: TRY/CATCH lets you intercept any error of severity 11–18 (and a few higher), the CATCH block calls ERROR_*() functions to capture context, XACT_STATE() tells you whether to ROLLBACK, and a bare THROW re-raises the original error so the caller still gets a real failure. Once those four steps are habit, your sprocs stop swallowing errors and start failing loudly.

The TRY/CATCH block structure.

BEGIN TRY
    -- the work
    BEGIN TRAN;
    INSERT INTO ...
    UPDATE ...
    COMMIT;
END TRY
BEGIN CATCH
    -- capture context
    DECLARE @err_num int      = ERROR_NUMBER();
    DECLARE @err_msg nvarchar(4000) = ERROR_MESSAGE();
    DECLARE @err_sev int      = ERROR_SEVERITY();
    DECLARE @err_state int    = ERROR_STATE();

    -- rollback if needed
    IF XACT_STATE() <> 0 ROLLBACK;

    -- log
    INSERT INTO dbo.ErrorLog(err_num, err_msg, err_sev, raised_at)
    VALUES(@err_num, @err_msg, @err_sev, SYSDATETIME());

    -- re-raise
    THROW;
END CATCH;

The six ERROR_*() functions inside CATCH.

• ERROR_NUMBER() — the SQL Server error number. Useful for branching on specific errors (e.g., 2627 is "primary key violation").
• ERROR_MESSAGE() — the human-readable error message.
• ERROR_SEVERITY() — severity level. 16+ is "user can fix"; 17+ is "resource error"; 20+ is "fatal".
• ERROR_STATE() — a 1-byte custom state; useful for the same error number in different code paths.
• ERROR_LINE() — the line within the batch where the error was raised. Helpful in long sprocs.
• ERROR_PROCEDURE() — the name of the sproc that raised the error. NULL for ad-hoc batches.

XACT_STATE() — the three transaction states.

Value	Meaning	What to do
1	Active committable transaction	You can COMMIT or ROLLBACK
0	No transaction	Nothing to roll back
-1	Active doomed transaction	Must ROLLBACK; cannot COMMIT

The "doomed" state (-1) is the gotcha: certain runtime errors put the transaction into a state where it cannot be committed — only rolled back. Trying to COMMIT raises another error. The canonical idiom is IF XACT_STATE() <> 0 ROLLBACK; — covers both 1 and -1.

SET XACT_ABORT ON — the cheap insurance.

• Without XACT_ABORT, only severity ≥ 20 errors and a few specific runtime conditions auto-abort the transaction. Lower-severity errors leave the transaction active and can lead to partial commits.
• With XACT_ABORT ON, most runtime errors auto-abort the entire batch and roll back the transaction. Combined with TRY/CATCH, this gives "fail closed" semantics by default.
• Always set it at the top of every sproc that runs a transaction. The cost is zero; the upside is "no partial commits ever."

THROW vs RAISERROR — pick THROW.

• THROW (2012+). Bare THROW; inside CATCH re-raises the original error with full fidelity (number, message, severity, line). The three-arg form THROW 50001, 'msg', 1; raises a new user error.
• RAISERROR. Pre-2012 idiom. Lets you format messages with placeholders (RAISERROR('user %s failed', 16, 1, @user)), but cannot re-raise the original error preserving the original number/severity. Still useful for parameterised user messages.
• Rule. Default to THROW. Reach for RAISERROR only when you need printf-style formatting of a user message.

Severity levels you actually care about.

Range	Class	Caught by TRY/CATCH?	Typical example
0–10	informational	No	PRINT, warnings
11–16	user fixable	Yes	constraint violations, divide by zero
17–19	resource / unrecoverable	Yes	out of memory, lock timeout
20–25	system fatal	No (connection drops)	hardware errors

The practical rule: TRY/CATCH catches 11–19. Severity 10 and below are informational, not errors. Severity 20+ usually drops the connection — you can't catch what you can't get back to.

The "doomed transaction" gotcha.

-- DOOMED — the transaction cannot be committed inside CATCH
BEGIN TRAN;

INSERT INTO dbo.PK_Table(id) VALUES(1);
INSERT INTO dbo.PK_Table(id) VALUES(1);  -- raises 2627 (PK violation)

-- If TRY/CATCH is around this, CATCH fires.
-- XACT_STATE() returns -1 (doomed).
-- COMMIT here raises 3930. Must ROLLBACK.

This is the single most common error-handling bug in legacy T-SQL: the CATCH block forgets to check XACT_STATE() and tries to keep working with an active transaction handle. Always check.

Worked example — the full TRY/CATCH + XACT_ABORT skeleton

Detailed explanation. Combine every primitive: SET XACT_ABORT ON, BEGIN TRAN, the work, COMMIT, then CATCH with XACT_STATE()-aware rollback, audit log insert, and bare THROW. This is the production-grade skeleton every senior T-SQL engineer writes from memory.

Question. Write dbo.usp_AccountDebit that subtracts an amount from an account, writes an audit row, and handles a PK violation, an insufficient-funds situation, and a generic runtime error — each with the right rollback and re-raise behaviour.

Input.

account_id	balance
1	500
2	50

Code.

CREATE OR ALTER PROCEDURE dbo.usp_AccountDebit
    @account_id int,
    @amount     decimal(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        UPDATE dbo.Accounts
           SET balance = balance - @amount
         WHERE account_id = @account_id
           AND balance   >= @amount;

        IF @@ROWCOUNT = 0
        BEGIN
            -- Friendly business error
            THROW 50100, 'insufficient funds or unknown account', 1;
        END

        INSERT INTO dbo.AccountAudit(account_id, delta, raised_at)
        VALUES(@account_id, -@amount, SYSDATETIME());

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        DECLARE @err_num   int             = ERROR_NUMBER();
        DECLARE @err_msg   nvarchar(4000)  = ERROR_MESSAGE();
        DECLARE @err_sev   int             = ERROR_SEVERITY();
        DECLARE @err_state int             = ERROR_STATE();
        DECLARE @err_proc  sysname         = ERROR_PROCEDURE();
        DECLARE @err_line  int             = ERROR_LINE();

        -- Rollback first if we have a transaction
        IF XACT_STATE() <> 0 ROLLBACK;

        -- Log to error table (outside the rolled-back transaction)
        INSERT INTO dbo.ErrorLog(err_num, err_msg, err_sev, err_state, err_proc, err_line, raised_at)
        VALUES(@err_num, @err_msg, @err_sev, @err_state, @err_proc, @err_line, SYSDATETIME());

        -- Re-raise to caller
        THROW;
    END CATCH;
END;

Step-by-step explanation.

1. SET XACT_ABORT ON ensures most runtime errors auto-abort the transaction. Combined with TRY/CATCH below, "fail closed" is the default.
2. BEGIN TRAN opens the transaction. The UPDATE checks both ownership (account_id) and sufficiency (balance >= @amount).
3. IF @@ROWCOUNT = 0 catches the "no row updated" case — either the account doesn't exist or the balance was too low. A custom THROW 50100, ... raises a user-defined error number with a friendly message.
4. On success, the audit INSERT writes a -@amount delta. COMMIT makes both writes durable. RETURN 0 signals success.
5. If anything inside TRY fired an error, CATCH runs. The ERROR_*() functions capture context into local variables (these stop being valid once another statement runs).
6. XACT_STATE() <> 0 ROLLBACK covers both committable (1) and doomed (-1) transactions. After ROLLBACK, XACT_STATE() returns 0.
7. The audit INSERT to dbo.ErrorLog runs outside the rolled-back transaction — so the log row survives even though the business transaction did not.
8. Bare THROW; re-raises the original error to the caller. The caller's EXEC line catches the exception with full fidelity (number, message, severity, line, sproc name).

Output. Three call scenarios:

Call	Result
EXEC dbo.usp_AccountDebit 1, 100	balance 500→400, audit row, RETURN 0
EXEC dbo.usp_AccountDebit 2, 100	ROLLBACK, ErrorLog row, THROW 50100
EXEC dbo.usp_AccountDebit 99, 10 (no account)	ROLLBACK, ErrorLog row, THROW 50100

Rule of thumb. Treat this skeleton as a template. The body changes per sproc; the four-step CATCH (capture / rollback / log / re-raise) does not. Every senior code review checks that THROW is bare in CATCH and that XACT_STATE is consulted before ROLLBACK.

Worked example — THROW vs RAISERROR for user-friendly messages

Detailed explanation. A sproc validates inputs and wants to raise a parameterised message back to the caller. THROW is the modern idiom; RAISERROR is still needed when the message has printf-style placeholders for variable values.

Question. Compare THROW 50001, 'invalid amount', 1 with RAISERROR('invalid amount: %s on account %d', 16, 1, @amount_str, @account_id). When do you reach for each?

Input. A bad call: @account_id = 42, @amount = -5.

Code.

-- Modern: THROW with a literal message
IF @amount < 0
    THROW 50001, 'amount must be non-negative', 1;

-- Modern: THROW with a pre-formatted message via FORMATMESSAGE / interpolation
DECLARE @msg nvarchar(2048) = CONCAT(
    'invalid amount ', @amount, ' on account ', @account_id);
IF @amount < 0
    THROW 50001, @msg, 1;

-- Legacy: RAISERROR with printf placeholders
IF @amount < 0
    RAISERROR('invalid amount: %d on account %d', 16, 1, @amount, @account_id);

Step-by-step explanation.

1. The simplest THROW takes a fixed integer error number (50000–2147483647 user range), a literal message, and a state. No formatting.
2. To embed variable values in a THROW message, build the message string with CONCAT or FORMATMESSAGE before THROW. The message has to be nvarchar.
3. RAISERROR accepts printf-style placeholders directly (%d, %s, %I64d). It's the legacy idiom but still convenient when the message has multiple values.
4. Bare THROW; inside CATCH preserves the original error fully — number, severity, line, procedure. RAISERROR cannot preserve those when re-raising; it raises a new error with severity 16 by default.
5. Rule. Default to THROW. Use RAISERROR only when the message must be formatted with placeholders.

Output.

Form	Caller sees
THROW 50001, 'amount must be non-negative', 1;	error number 50001, fixed message
THROW 50001, @msg, 1; after CONCAT	error number 50001, dynamic message
RAISERROR('amount: %d on acct %d', 16, 1, @amt, @aid);	error number 50000, formatted message
Bare THROW; in CATCH	original error, full fidelity

Rule of thumb. THROW is the default for raising and re-raising. Use RAISERROR only when the formatted-message convenience pays for the legacy semantics. Never use RAISERROR to re-raise inside CATCH — it loses the original error number.

Worked example — handling a doomed transaction

Detailed explanation. Some errors leave the transaction in XACT_STATE() = -1 (doomed). The transaction is alive but cannot be committed. Trying to COMMIT raises 3930. The CATCH block must detect the doomed state and ROLLBACK; it cannot keep working.

Question. Trigger a doomed transaction (PK violation under XACT_ABORT ON) and write the CATCH block that correctly handles it. Show what happens if you forget the XACT_STATE check.

Input. A duplicate insert.

@id
1 (already exists)

Code.

CREATE OR ALTER PROCEDURE dbo.usp_DoomedDemo
    @id int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        INSERT INTO dbo.PK_Table(id) VALUES(@id);   -- raises 2627 on duplicate

        COMMIT;
    END TRY
    BEGIN CATCH
        -- Inspect state
        DECLARE @state int = XACT_STATE();
        -- @state = -1 (doomed) under XACT_ABORT ON when 2627 fires

        IF @state <> 0
            ROLLBACK;

        -- DO NOT try to COMMIT here — would raise 3930
        THROW;
    END CATCH;
END;

Step-by-step explanation.

1. With SET XACT_ABORT ON, a PK violation (error 2627) auto-aborts the batch and dooms the transaction. XACT_STATE() returns -1.
2. The CATCH block reads XACT_STATE() into a local. It's -1, so the IF branch fires and ROLLBACK clears the doomed transaction.
3. The bug if you forget the check. A COMMIT here would raise 3930 ("transaction is doomed and only ROLLBACK is allowed"). A second INSERT here would raise 3930 for the same reason. Anything other than ROLLBACK fails.
4. After ROLLBACK, XACT_STATE() is 0. The CATCH can keep doing non-transactional work (audit log to a separate table) before re-raising.
5. Recovery rule. Inside CATCH, the first statement should be the rollback decision. Subsequent statements run cleanly.

Output.

XACT_STATE() at CATCH entry	Correct CATCH first move
1 (committable)	ROLLBACK (or COMMIT if you're keeping the work, which is rare)
0 (no tran)	nothing — there's nothing to roll back
-1 (doomed)	ROLLBACK (mandatory; nothing else works)

Rule of thumb. The CATCH block's first non-declaration statement is always the rollback decision: IF XACT_STATE() <> 0 ROLLBACK;. Memorise it. The rest of the block is auditing and re-raising.

SQL interview question on error handling

A senior interviewer often asks: "Walk me through your error-handling skeleton for a write sproc. Why XACT_ABORT? Why bare THROW? How do you log without losing the log on rollback?"

Solution Using the four-step contract

CREATE OR ALTER PROCEDURE dbo.usp_GoldStandardWrite
    @customer_id int,
    @total       decimal(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;

        IF @customer_id IS NULL
            THROW 50100, 'customer_id is required', 1;
        IF @total IS NULL OR @total < 0
            THROW 50101, 'total must be a non-negative number', 1;

        INSERT INTO dbo.Orders(customer_id, total)
        VALUES(@customer_id, @total);

        COMMIT;
        RETURN 0;
    END TRY
    BEGIN CATCH
        -- 1) capture
        DECLARE @num int      = ERROR_NUMBER();
        DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @sev int      = ERROR_SEVERITY();
        DECLARE @st  int      = ERROR_STATE();
        DECLARE @ln  int      = ERROR_LINE();
        DECLARE @pr  sysname  = ERROR_PROCEDURE();

        -- 2) rollback first
        IF XACT_STATE() <> 0 ROLLBACK;

        -- 3) log outside the rolled-back transaction
        INSERT INTO dbo.ErrorLog(err_num, err_msg, err_sev, err_state, err_line, err_proc, raised_at)
        VALUES(@num, @msg, @sev, @st, @ln, @pr, SYSDATETIME());

        -- 4) re-raise
        THROW;
    END CATCH;
END;

Step-by-step trace.

Step	Statement	Channel impacted
1	SET NOCOUNT / XACT_ABORT ON	session settings
2	BEGIN TRAN	opens transaction
3	Input validations THROW 50100/50101	jump to CATCH on bad input
4	INSERT INTO Orders	data write
5	COMMIT; RETURN 0	success path
6	CATCH: capture six ERROR_*() values	local snapshot
7	CATCH: IF XACT_STATE() <> 0 ROLLBACK;	rollback if active
8	CATCH: INSERT into ErrorLog	log survives because it's after ROLLBACK
9	CATCH: bare THROW;	re-raise to caller

The trace shows the exact ordering. Capture first (before any statement clobbers the ERROR_*() context), rollback second, log third (so the log row isn't itself rolled back), re-raise last.

Output:

return_code	ErrorLog row written?	Caller sees
0 (success)	no	nothing thrown
(THROW raised)	yes	original error, full fidelity

Why this works — concept by concept:

• SET XACT_ABORT ON — promotes most runtime errors to "auto-rollback the batch." Combined with TRY/CATCH, this gives "fail closed by default" semantics — the opposite of partial commits.
• Validate before work — THROW 50100 on invalid inputs runs inside TRY but raises immediately. Cleaner than IF ... RETURN 1, because the error path is the same whether the error is user-caused or runtime-caused.
• Capture ERROR_*() into locals — once any statement runs inside CATCH, the ERROR_*() functions reset. Snapshot them first.
• XACT_STATE-aware ROLLBACK — covers both committable and doomed transactions. The <> 0 form is the canonical safe rollback.
• Log after ROLLBACK — the log INSERT runs outside the rolled-back transaction, so the audit row survives even though the business write didn't. Critical for incident postmortems.
• Bare THROW — re-raises the original error with full fidelity (number, severity, line, sproc). The caller's exception handler sees the real error, not a generic "RAISERROR 50000".
• Cost — one transaction; one rollback decision; one log row per failure; one re-raise. Constant overhead per sproc invocation.

SQL
Topic — exception handling
Exception handling problems (SQL)

Practice →

SQL
Topic — defensive coding
Defensive coding problems (SQL)

Practice →

---

Cheat sheet — T-SQL sproc recipes

• The four-line preamble. Every sproc starts with SET NOCOUNT ON; SET XACT_ABORT ON; — and modern shops add SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; to defend against legacy session settings. Two lines you type, two more that documentation rewards.
• The error skeleton. BEGIN TRY BEGIN TRAN ... COMMIT; RETURN 0; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK; THROW; END CATCH; — paste this template, fill the middle, ship it.
• Safe dynamic SQL. EXEC sp_executesql @sql, N'@id int', @id = @id; — every value is bound, never concatenated. The EXEC(@sql) form is for catalog-only DDL with zero user input.
• Identifier whitelist for dynamic SQL. QUOTENAME(@col) for escape + IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @col) THROW 50010, ... for validation. Both together; never one alone.
• OUTPUT parameter call site. DECLARE @out int; EXEC dbo.usp_X @in = 1, @out = @out OUTPUT; — the OUTPUT keyword on the call site is required. Forgetting it silently discards the return.
• Return code capture. DECLARE @rc int; EXEC @rc = dbo.usp_X @in = 1; — the @rc = EXEC ... form captures the integer return code. Default is 0 if no explicit RETURN.
• Re-raise inside CATCH. Bare THROW; (no arguments) preserves the original error number, message, severity, line, and procedure. RAISERROR loses fidelity.
• TVP from .NET. Pass a DataTable with SqlDbType.Structured and TypeName = "dbo.OrderLineRows". Sproc declares @rows dbo.OrderLineRows READONLY. One round-trip for N rows.
• OPTION (RECOMPILE) for parameter sniffing. Stick it on the query inside the sproc where the plan is unstable across parameter ranges. Costs a per-call compile; cures "fast for some users, slow for others."
• CREATE OR ALTER PROCEDURE. SQL Server 2016 SP1+ ships the idempotent form. Use it; drop the IF OBJECT_ID(...) DROP PROCEDURE ceremony.
• GRANT EXECUTE for security boundary. GRANT EXECUTE ON dbo.usp_OrderCreate TO app_user; — combined with no direct table grants, the sproc becomes the only door. Ownership chaining handles the table writes inside.
• Audit log outside the rolled-back transaction. Inside CATCH, do ROLLBACK before inserting into ErrorLog. The log row survives because it runs outside the (rolled-back) business transaction.
• THROW with a custom error number. THROW 50100, 'message', 1; raises a user error in the 50000+ range. The caller can catch the specific number and branch on it. Add to sys.messages with sp_addmessage for centralised message storage.

Frequently asked questions

How do I return multiple values from a stored procedure?

T-SQL gives you three channels in one EXEC call, and you can mix them freely. Use a SELECT (without INTO) inside the body for tabular results — any number of rows, any shape. Use OUTPUT parameters for typed scalar returns — declare them with OUTPUT on the header and on the call site, then SET @out = expr inside. Use RETURN n for a single 32-bit status code — by convention 0 for success and non-zero for error. If you need two scalars back, declare two OUTPUT parameters; that is the idiomatic answer. Avoid stuffing data into the RETURN code — it is int only and reserved for coarse status. For a worked stored procedure example sql of all three channels, see the "anatomy" section above.

Should I use sp_executesql or EXEC for dynamic SQL?

Use sp_executesql by default — always. It supports parameter binding (so values cannot be confused with SQL syntax, eliminating SQL injection), it preserves the plan cache (one cached plan, regardless of parameter values), and it is the auditable form that code reviewers can recognise. Reserve EXEC(@sql) for the narrow case of deployment-time DDL with no user input — catalog-driven index rebuilds, partition rotation, environment-specific schema operations. Any EXEC(@sql) touching a user-influenced value is a bug. The sp_executesql rewrite is mechanical: change EXEC(@sql) to EXEC sp_executesql @sql, N'@p1 int, @p2 varchar(20)', @p1 = @p1, @p2 = @p2;. Two lines of change; orders-of-magnitude safer.

Is TRY/CATCH enough, or do I still need XACT_ABORT?

You need both. TRY/CATCH lets you intercept errors of severity 11–18 and write a recovery branch; without it, the error propagates to the caller and any in-flight transaction is left to the default behaviour. SET XACT_ABORT ON changes that default behaviour: most runtime errors now auto-abort the batch and roll back the transaction, instead of leaving a half-applied transaction active. Together they give "fail closed" semantics — TRY/CATCH catches the error, XACT_ABORT ensures the rollback is already done by the time CATCH runs, and the CATCH block can audit, re-raise, and exit cleanly. Without XACT_ABORT, you have to handle partial-commit recovery in every CATCH block. With it, the worst case is "doomed transaction" — which IF XACT_STATE() <> 0 ROLLBACK; handles in one line.

THROW vs RAISERROR — which should I use?

Default to THROW (added in SQL Server 2012). It has cleaner syntax, preserves the original error number and severity when re-raising inside CATCH (just write bare THROW;), and integrates with sys.messages for centralised error catalogues. Reach for RAISERROR only in two cases: (a) you need printf-style placeholders inside the message text (RAISERROR('user %s on account %d failed', 16, 1, @user, @acct)), or (b) you are supporting SQL Server versions older than 2012 (rare in 2026). Never use RAISERROR to re-raise inside CATCH — it loses the original error number and forces severity 16. Bare THROW; is the canonical re-raise.

Why does my stored procedure run slow only for some parameters?

This is parameter sniffing. SQL Server compiles the sproc on first execution using the parameter values seen at compile time, then caches that plan. Subsequent calls reuse the plan even when the new parameters have very different selectivity — a sproc compiled with a "rare customer" (1 row matches) reuses the same plan when called for a "popular customer" (1M rows match), and the plan optimised for 1 row is catastrophic for 1M. Fixes, in order of preference: add OPTION (RECOMPILE) to the troublesome statement (forces a fresh plan per call, costs per-call compile time), use OPTIMIZE FOR (@p = value) to pin a plan to a known-good parameter, use OPTION (OPTIMIZE FOR UNKNOWN) to let the optimiser use density estimates instead of the sniffed value, or refactor the sproc into multiple narrower sprocs each suited to its parameter range. The "fast for some users, slow for others" symptom is the diagnostic giveaway.

Can a stored procedure call itself recursively?

Yes — SQL Server supports recursive sproc invocation up to the configured @@NESTLEVEL limit (default 32). Each recursive call adds one to @@NESTLEVEL; exceeding 32 raises an error. Use cases: tree traversal (employee/manager hierarchies, parts-of-parts BOMs) and divide-and-conquer batches. The base case must terminate before nest level 32; otherwise the sproc raises 217. Recursive sprocs share many issues with recursive functions: each level is a new stack frame, parameter sniffing applies at each level, and TRY/CATCH at each level can catch errors from the level below. Modern T-SQL more often handles tree traversal with a recursive CTE (WITH RECURSIVE ...) inside a regular sproc rather than a self-calling sproc — the CTE is set-based, faster, and not limited by nest level. Reach for sproc recursion only when each recursive step needs its own transaction or its own server-side branching.

Practice on PipeCode

• Drill the subqueries practice library → for the multi-statement sproc-shaped silhouettes that pop up in T-SQL interviews.
• Rehearse Microsoft SQL interview problems → for the dialect-specific T-SQL probes — sprocs, XACT_ABORT, sp_executesql, and identifier escape.
• Sharpen the error muscles with exception handling drills → for TRY/CATCH and XACT_STATE recovery patterns.
• Layer the defensive coding library → for the "audit your inputs and your transactions" mindset.
• Stack the SQL generation library → for safe dynamic SQL — sp_executesql + QUOTENAME patterns.
• For the broader interview surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the dialect axis with the SQL for data engineering interviews course →.
• For long-form schema craft, work through data modelling for DE interviews →.

Pipecode.ai is Leetcode for Data Engineering — every T-SQL recipe above ships with hands-on practice rooms where you write the `sp_executesql` rewrite, the OUTPUT parameter wiring, and the TRY/CATCH + XACT_ABORT skeleton against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so your next `stored procedure example sql` interview answer ships with a clean rollback decision, a bound parameter, and a re-raised `THROW` — not a half-baked memory of "I think you use SET XACT_ABORT somewhere."

Practice Microsoft SQL interviews →
Exception handling drills →