Stored Procedure Interview Questions

Both are named, reusable blocks of T-SQL stored in the database, but they exist for different jobs. A stored procedure is built to do work: it can run INSERT, UPDATE and DELETE statements, control transactions with BEGIN TRAN and COMMIT, and return zero, one or several result sets. A function is built to compute and return a value, either a single scalar value or a table, and it is meant to be side effect free.

The most practical difference is where you can use each one. A function can be called inline as part of a query, so it composes naturally into the SELECT list, a WHERE predicate, a JOIN or a computed column. A procedure cannot: you invoke it on its own with the EXEC (or EXECUTE) statement, and its output is not something you can drop into the middle of another query.

• Data changes: a procedure can modify data; a function generally cannot (a scalar or inline table valued function is not allowed to change table data).
• Return contract: a function must return a value; a procedure need not, though it can pass back status through an integer return code and OUTPUT parameters.
• Result sets: a procedure can return multiple result sets; a function returns exactly one scalar or one table.
• Transactions: a procedure can start and control transactions; a function cannot manage transactions.
• Invocation: a procedure is called with EXEC; a function is used inline inside a query.

These routines line up with the categories of SQL commands (DDL, DML, DCL, TCL, DQL): procedures freely mix DML and TCL to change and commit data, while functions stay on the read side so they are safe to embed in a query.

One important caveat is performance. A traditional scalar function called once per row can force row by row execution and block parallelism, which makes large queries slow. Prefer an inline table valued function, which the optimizer can expand into the surrounding plan, and note that SQL Server 2019 and later can inline many scalar functions automatically. When the task is really about changing data or running a multi step transaction, reach for a stored procedure instead.

CREATE PROCEDURE dbo.PlaceOrder
    @CustomerId INT,
    @Total      DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRAN;
        INSERT INTO dbo.Orders (customer_id, total, created_at)
        VALUES (@CustomerId, @Total, SYSUTCDATETIME());

        UPDATE dbo.Customers
        SET order_count = order_count + 1
        WHERE customer_id = @CustomerId;
    COMMIT TRAN;

    SELECT SCOPE_IDENTITY() AS new_order_id;
END;
GO

EXEC dbo.PlaceOrder @CustomerId = 42, @Total = 99.50;

CREATE FUNCTION dbo.fn_LineTotal
(
    @Quantity  INT,
    @UnitPrice DECIMAL(10,2)
)
RETURNS DECIMAL(12,2)
AS
BEGIN
    RETURN @Quantity * @UnitPrice;
END;
GO

SELECT item_id,
       dbo.fn_LineTotal(quantity, unit_price) AS line_total
FROM dbo.OrderItems
WHERE dbo.fn_LineTotal(quantity, unit_price) > 100;

Related: What is the difference between a scalar and a table valued function? · When should you use a stored procedure instead of application code? · What is a trigger and when should you use one?

A stored procedure communicates with its caller through parameters. By default a parameter is an input parameter: the caller supplies a value that the procedure reads but cannot send back. Add the OUTPUT keyword and the parameter becomes an output parameter, a channel the procedure writes to so the caller can read the result after execution.

Input parameters are declared after the procedure name with a data type. If you give one a default value, it becomes optional, so callers can omit it and fall back to the default. Without a default, the parameter is required, and calling the procedure without it raises error 201 (procedure expects parameter which was not supplied).

• OUTPUT parameter must carry the OUTPUT keyword in two places: on the parameter in the CREATE PROCEDURE header, and again on the argument in the EXEC call. Miss the keyword on the EXEC side and the procedure still runs, but the caller variable stays NULL because the value is never handed back.
• The caller passes a variable (not a literal) for an OUTPUT argument, because the procedure needs somewhere to write the result.

SQL Server lets you pass arguments by position (in declared order) or by name using @param = value. Passing by name is clearer, order-independent, and lets you skip optional parameters that have defaults. Once you name one argument, every argument after it must also be named.

Use OUTPUT parameters when a procedure needs to return one or a few scalar values alongside its work, such as a computed total or a new identity value. To learn when a plain return code is the better choice, compare output parameters against the RETURN value.

CREATE PROCEDURE usp_OrderTotal
    @CustomerId INT,                 -- required input
    @IncludeTax BIT = 1,             -- optional input (has a default)
    @Total MONEY OUTPUT              -- returns a value to the caller
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Total = SUM(o.Amount) * (CASE WHEN @IncludeTax = 1 THEN 1.08 ELSE 1 END)
    FROM Orders AS o
    WHERE o.CustomerId = @CustomerId;
END;

DECLARE @Result MONEY;

-- Passing by name; OUTPUT keyword is required on the EXEC side too
EXEC usp_OrderTotal
    @CustomerId = 42,
    @Total = @Result OUTPUT;   -- @IncludeTax omitted, uses its default

SELECT @Result AS OrderTotal;

Related: What is the difference between an output parameter and a return value? · What is the difference between a stored procedure and a function? · How do you handle errors in T-SQL with TRY CATCH?

T-SQL structured error handling uses a BEGIN TRY ... END TRY block immediately followed by a BEGIN CATCH ... END CATCH block. If any statement inside the TRY block raises a runtime error, execution stops at that point and control jumps straight to the CATCH block. If nothing fails, the CATCH block is skipped entirely.

Inside the CATCH block you have access to a family of functions that describe what went wrong: ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE() and ERROR_PROCEDURE(). These are only meaningful inside a CATCH block and let you log a precise diagnostic instead of a generic message.

When the failing code was part of a transaction, the CATCH block is responsible for cleaning up. Check XACT_STATE() before rolling back: it returns 1 for an open, committable transaction, -1 for an uncommittable (doomed) transaction that can only be rolled back, and 0 when there is no active transaction. Only call ROLLBACK when the state is non-zero, otherwise you raise a second error for rolling back a transaction that does not exist.

After you have logged and rolled back, re-raise the error so the caller is not left thinking the operation succeeded. The modern way is THROW with no arguments, which re-throws the original error, preserving its number and line. The older RAISERROR works too but cannot reproduce the original error number the way a bare THROW can.

A strong habit is to add SET XACT_ABORT ON at the top of the procedure. This tells SQL Server to automatically mark the whole transaction as uncommittable when a runtime error occurs, which prevents you from accidentally committing partial work and makes the XACT_STATE check reliable.

• What TRY CATCH does NOT catch: compile errors and syntax errors in the same batch (the batch never starts), object-name resolution errors from deferred name resolution in the same scope, and statement-level recompile errors.
• It also does not catch errors with a severity of 20 or higher that close the connection, nor most attention events such as a client cancel or a query timeout.
• Errors raised inside a called stored procedure or dynamic SQL are caught, because they happen in a separate scope that the outer TRY can observe.

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Only roll back if a transaction is still open
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    SELECT
        ERROR_NUMBER()  AS error_number,
        ERROR_LINE()    AS error_line,
        ERROR_MESSAGE() AS error_message;
END CATCH;

CREATE PROCEDURE dbo.TransferFunds
    @from_id INT,
    @to_id   INT,
    @amount  DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Accounts SET balance = balance - @amount WHERE account_id = @from_id;
        UPDATE Accounts SET balance = balance + @amount WHERE account_id = @to_id;

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

        INSERT INTO ErrorLog (error_number, error_message, logged_at)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), SYSUTCDATETIME());

        -- Re-raise the original error to the caller
        THROW;
    END CATCH;
END;

Related: How do you use a transaction inside a stored procedure? · What is a transaction and what does ACID mean? · How do input and output parameters work in a stored procedure?

A transaction groups several statements so they either all succeed or all fail as a unit, which is what the atomicity guarantee in ACID means. Inside a stored procedure you open the transaction with BEGIN TRANSACTION, do the work, and finish with COMMIT. If a statement raises an error you must undo everything already done with ROLLBACK, otherwise the procedure leaves the database half-updated.

The robust pattern wraps the work in a TRY...CATCH block. The TRY block runs BEGIN TRAN, the DML, then COMMIT. If any statement throws, control jumps to the CATCH block, where you ROLLBACK and then re-raise the error so the caller knows it failed. Without the rollback the open transaction keeps holding locks and blocks other sessions, a common cause of the problems covered in locks and deadlocks.

• SET XACT_ABORT ON at the top of the procedure. This makes SQL Server abort the whole batch and roll back the transaction automatically on most runtime errors (a constraint violation, a lock timeout, a deadlock victim). Without it, some errors only abort the single statement and leave the transaction open.
• Check the state before rolling back. In the CATCH block, only call ROLLBACK if a transaction is actually still active. Use XACT_STATE() or @@TRANCOUNT; calling ROLLBACK when no transaction is open raises its own error.
• XACT_STATE() returns three values. 1 means an active, committable transaction (you can commit or roll back), -1 means an uncommittable or doomed transaction (you can only roll back), and 0 means there is no transaction at all.

Prefer XACT_STATE() over a bare @@TRANCOUNT check because a transaction can be doomed (uncommittable) while @@TRANCOUNT is still greater than zero; in that state a COMMIT would fail and only a ROLLBACK is legal. Re-raising the original error with THROW (or RAISERROR on older versions) lets the calling application handle the failure instead of silently swallowing it. See Microsoft Learn on TRY...CATCH for the full error functions.

CREATE OR ALTER PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(12, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;   -- abort and roll back on any runtime error

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Accounts
        SET balance = balance - @Amount
        WHERE account_id = @FromAccount;

        UPDATE dbo.Accounts
        SET balance = balance + @Amount
        WHERE account_id = @ToAccount;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Only roll back if a transaction is still open
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;   -- re-raise the original error to the caller
    END CATCH
END;

-- Without XACT_ABORT ON, some errors leave the transaction open:
BEGIN TRANSACTION;
    INSERT INTO dbo.Orders (order_id, total) VALUES (1, 100);
    INSERT INTO dbo.Orders (order_id, total) VALUES (1, 200); -- PK violation
    -- statement fails, but the transaction is STILL OPEN and holding locks
COMMIT TRANSACTION;   -- this wrongly commits the first row

-- With XACT_ABORT ON, the same error aborts the whole batch
-- and rolls the transaction back automatically:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO dbo.Orders (order_id, total) VALUES (1, 100);
    INSERT INTO dbo.Orders (order_id, total) VALUES (1, 200); -- PK violation
    -- batch aborts here; nothing is left committed or locked
COMMIT TRANSACTION;

Related: How do you handle errors in T-SQL with TRY CATCH? · How do nested transactions behave in stored procedures? · What is a transaction and what does ACID mean?

Dynamic SQL is SQL text that is assembled as a string while the batch is running and then executed, rather than being written literally in the procedure. In T-SQL you run it with either EXEC(@sql) or, preferably, sp_executesql @sql. Because the statement text is not fixed until runtime, it lets a single procedure adapt its behavior to values that are only known when it is called.

You reach for dynamic SQL when something that normally has to be hard coded needs to vary at runtime:

• Object names that change, such as a table or schema chosen by a parameter, since you cannot parameterize an identifier the way you parameterize a value.
• Dynamic filtering and sorting, for example an optional-parameter search screen where the WHERE or ORDER BY clause is built from whichever inputs the user supplied.
• Dynamic pivots, where the list of columns to pivot on is derived from the data itself.
• Administrative or metadata-driven scripts that loop over many tables and generate statements for each.

The important rule is how you build it. Always prefer sp_executesql with declared parameters over gluing user input into the string with +. Passing values as parameters keeps the SQL text stable, which lets SQL Server cache and reuse a single execution plan, and it removes the SQL injection risk that string concatenation creates. Only genuine identifiers (table or column names) need to be inlined, and those should be validated or wrapped with QUOTENAME first.

The danger of concatenating raw input is real: a crafted value can change the meaning of your query and expose or destroy data. See SQL injection explained and how to prevent it for how the attack works and why parameters defeat it.

DECLARE @sql   nvarchar(max);
DECLARE @city  varchar(50) = 'London';

SET @sql = N'SELECT customer_id, name, city
             FROM Customers
             WHERE city = @city_param
             ORDER BY name;';

EXEC sp_executesql
    @sql,
    N'@city_param varchar(50)',
    @city_param = @city;

DECLARE @sql    nvarchar(max);
DECLARE @table  sysname = 'Orders';
DECLARE @status varchar(20) = 'Open';

-- Identifier must be inlined, so validate it with QUOTENAME.
SET @sql = N'SELECT COUNT(*) AS open_rows
             FROM ' + QUOTENAME(@table) + N'
             WHERE status = @status_param;';

EXEC sp_executesql
    @sql,
    N'@status_param varchar(20)',
    @status_param = @status;

Related: How do you prevent SQL injection in dynamic SQL? · How do PIVOT and UNPIVOT work? · How does plan caching and recompilation work for stored procedures?

SQL injection happens when user input is glued directly into a SQL string so that the input can change the meaning of the statement. Dynamic SQL is the most common place this goes wrong, because you are literally building a command out of text. The golden rule is simple: treat user input as data, never as code. For background see SQL injection explained and how to prevent it.

The safe way to run dynamic SQL in SQL Server is sp_executesql with typed parameters. You write placeholders like @id in the SQL text and pass the real values separately. Because the value never becomes part of the parsed statement, a string like 105 OR 1=1 is compared as a literal, not executed as logic. As a bonus, parameterized statements let SQL Server cache and reuse the query plan.

• Parameterize values with sp_executesql and declare each parameter with its correct type; do not concatenate.
• Quote identifiers that must be dynamic (table or column names cannot be parameters) with QUOTENAME(), which safely wraps a name in brackets and escapes any embedded brackets.
• Validate against an allow-list: if only a fixed set of sort columns or table names is legal, check the input against that list and reject anything else.
• Least privilege: run the code under an account that can only touch what it needs, so a successful injection does limited damage.

Remember that QUOTENAME() is for identifiers (object names), while parameters are for values. Trying to pass a table name as an sp_executesql parameter will not work, and building an ORDER BY from raw user text is a classic hole. Combine these techniques rather than relying on any one. More context lives on the SQL security page.

Filtering out quotes or blacklisting keywords like DROP is not a real defense; attackers bypass blacklists easily and legitimate data (an O'Brien surname) breaks. Parameterization is the only approach that is correct by construction.

-- DO NOT DO THIS
DECLARE @input NVARCHAR(100) = N'105 OR 1=1';  -- from the app
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT * FROM dbo.Customers WHERE customer_id = ' + @input;

EXEC (@sql);
-- The OR 1=1 is now part of the statement and returns every row.

DECLARE @input INT = 105;                 -- typed as data
DECLARE @sortCol SYSNAME = N'name';       -- an identifier, not a value

-- Allow-list the dynamic column name before using it.
IF @sortCol NOT IN (N'name', N'customer_id', N'created_at')
    THROW 50000, 'Invalid sort column', 1;

DECLARE @sql NVARCHAR(MAX) =
    N'SELECT customer_id, name FROM dbo.Customers'
  + N' WHERE customer_id = @id'
  + N' ORDER BY ' + QUOTENAME(@sortCol);   -- QUOTENAME wraps the identifier

EXEC sys.sp_executesql
     @sql,
     N'@id INT',      -- parameter definition
     @id = @input;    -- value passed as data, never parsed as code

Related: What is dynamic SQL and when should you use it? · When should you use a stored procedure instead of application code? · How do you handle errors in T-SQL with TRY CATCH?

A trigger is a special kind of stored procedure that the database engine executes automatically when a specific event happens. You never call it directly; it fires as a side effect of another action. The most common kind is a DML trigger, which responds to data changes: INSERT, UPDATE or DELETE on a table or view.

DML triggers come in two timings. An AFTER (also written FOR) trigger runs once the statement has passed constraint checks and the change is in progress, which is ideal for logging what happened. An INSTEAD OF trigger replaces the original action entirely and is mainly used to make otherwise non updatable views accept modifications.

Inside a DML trigger you read the change through two special pseudo-tables: inserted holds the new versions of affected rows (available for INSERT and UPDATE) and deleted holds the old versions (available for DELETE and UPDATE). An UPDATE exposes both, so comparing inserted and deleted tells you exactly what changed. These tables live only for the life of the trigger.

Beyond DML there are DDL triggers, which fire on schema events such as CREATE TABLE or ALTER, and logon triggers, which fire when a session connects. Both are useful for governance and security auditing rather than everyday data work.

• Good uses: writing an audit trail of who changed what and when, or enforcing a complex business rule that a simple CHECK constraint cannot express.
• Cautions: trigger logic is hidden from anyone reading the calling code, so it can surprise developers; it must be written set based because it fires per statement, not per row (one statement that touches 500 rows fires the trigger once with 500 rows in inserted); and heavy work inside a trigger slows down every write and lengthens the transaction.

A common alternative for capturing changes without hand written triggers is to model an history table pattern or use built in temporal features. Reach for a trigger when the reaction to a data change must be automatic and guaranteed, and keep the body short. See the CREATE TRIGGER documentation for the full syntax.

CREATE TABLE OrderAudit (
    audit_id   INT IDENTITY PRIMARY KEY,
    order_id   INT,
    total      DECIMAL(10,2),
    action     VARCHAR(10),
    changed_by SYSNAME,
    changed_at DATETIME2
);
GO

CREATE TRIGGER trg_Orders_Insert
ON Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO OrderAudit (order_id, total, action, changed_by, changed_at)
    SELECT i.order_id, i.total, 'INSERT', SUSER_SNAME(), SYSDATETIME()
    FROM inserted AS i;
END;

CREATE TRIGGER trg_Orders_Update
ON Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO OrderAudit (order_id, total, action, changed_by, changed_at)
    SELECT i.order_id, i.total, 'UPDATE', SUSER_SNAME(), SYSDATETIME()
    FROM inserted AS i
    INNER JOIN deleted AS d ON d.order_id = i.order_id
    WHERE i.total <> d.total;
END;

Related: How do you model audit history or change tracking? · What is the difference between a stored procedure and a function? · How do you handle errors in T-SQL with TRY CATCH?

Both are user defined functions, but they differ in what they return and how the optimizer treats them. A scalar function returns exactly one value (an int, varchar, date, and so on) and is used wherever a single expression is allowed, such as a SELECT list or a WHERE predicate. A table valued function returns a whole result set, so you use it in the FROM clause and query it just like a table or a view.

There are two flavors of TVF and the difference matters a lot for performance:

• Inline TVF is a single RETURN (SELECT ...) with no BEGIN/END body. The optimizer inlines its definition into the calling query, so it behaves like a parameterized view and can use the real statistics of the underlying tables. This is the fast, preferred form.
• Multi-statement TVF declares a @table variable, fills it with one or more statements, then returns it. Because the engine cannot see inside it at plan time, it assumes a fixed low row estimate (1 row on older versions, 100 on newer ones), which frequently produces bad plans when the real output is large.

Scalar functions carry their own classic performance trap: when referenced in a query they are typically invoked once per row, which serializes execution and hides cost from the plan. SQL Server 2019 added scalar UDF inlining to fold many scalar functions into the surrounding query automatically, but it does not cover every function. When performance matters, rewrite a row-by-row scalar function as an inline TVF and call it with CROSS APPLY, which lets the optimizer produce a set based plan and use supporting indexes effectively.

Rule of thumb: reach for an inline TVF first, avoid multi-statement TVFs on hot paths, and treat scalar functions in large scans with suspicion.

CREATE FUNCTION dbo.fn_OrderTotal (@order_id INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @total DECIMAL(10,2);
    SELECT @total = SUM(quantity * unit_price)
    FROM OrderLines
    WHERE order_id = @order_id;
    RETURN ISNULL(@total, 0);
END;
GO

-- Called once per row: cheap on 10 rows, painful on 10 million
SELECT o.order_id, dbo.fn_OrderTotal(o.order_id) AS total
FROM Orders AS o;

CREATE FUNCTION dbo.fn_OrderTotals (@order_id INT)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(quantity * unit_price) AS total
    FROM OrderLines
    WHERE order_id = @order_id
);
GO

-- Optimizer inlines the SELECT and builds one set based plan
SELECT o.order_id, t.total
FROM Orders AS o
CROSS APPLY dbo.fn_OrderTotals(o.order_id) AS t;

Related: What is the difference between a stored procedure and a function? · What is CROSS APPLY and how is it different from a JOIN? · When should you use a stored procedure instead of application code?

A table valued parameter (TVP) is a parameter that carries an entire set of rows into a stored procedure or function in a single call. Instead of passing one scalar value, you pass a small table. It is built on a user-defined table type that you create with CREATE TYPE ... AS TABLE, which fixes the columns and types the parameter will accept.

TVPs are useful because they solve the classic problem of sending a list into the database. The usual bad alternatives are either calling the proc once per row (which triggers the N+1 round-trip problem) or packing the values into a comma separated string and parsing it inside the proc. A TVP replaces both with one strongly typed, set-based call.

• One round-trip: send hundreds of rows in a single EXEC rather than many network calls.
• Strongly typed: the table type declares real columns, so there is no fragile string parsing or implicit conversion.
• Set-based: inside the proc the parameter behaves like a table you can JOIN, filter, or INSERT ... SELECT from.
• Reusable: the same table type can back many procedures.

There are two rules to remember. First, the parameter must be marked READONLY: you cannot update the contents of a TVP inside the procedure, and you cannot use it as an output parameter (this is a common contrast with ordinary input and output parameters). Second, from application code you fill the type as a table variable or a DataTable and pass it in one shot.

TVPs shine for bulk operations such as inserting many detail lines for an order, applying a list of ID filters, or staging rows for a merge. For very large loads (millions of rows) dedicated bulk copy is still faster, but for typical batch sizes a TVP is the clean, safe choice.

-- 1. Define a user-defined table type (the shape of the parameter)
CREATE TYPE dbo.UserList AS TABLE (
    user_id INT           NOT NULL,
    user_name NVARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id)
);
GO

-- 2. A proc that takes the whole set as ONE read-only parameter
CREATE PROCEDURE dbo.usp_AddUsers
    @rows dbo.UserList READONLY   -- TVPs must be READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Users (user_id, user_name)
    SELECT user_id, user_name
    FROM @rows;                   -- treat it like any table
END;

-- Declare a variable OF the table type, load rows, pass it once
DECLARE @batch dbo.UserList;

INSERT INTO @batch (user_id, user_name) VALUES
    (1, N'Ada'),
    (2, N'Ben'),
    (3, N'Cy');

EXEC dbo.usp_AddUsers @rows = @batch;   -- three rows, one round-trip

Related: How do input and output parameters work in a stored procedure? · What is the difference between a stored procedure and a function? · What is the N plus one query problem and how do you fix it?

A stored procedure is a named block of SQL that lives in the database and runs on the server. The question is not "procedures or application code" in the abstract, but where a given piece of logic belongs. The strongest case for a procedure is multi-statement, data-intensive logic that benefits from being close to the data.

• Fewer round-trips. If an operation is five statements that each read or write rows, running them in a procedure means one call from the app instead of five network hops. For chatty batch work and loops this alone can dominate performance.
• Transactions and consistency. Wrapping several writes in a single BEGIN TRAN / COMMIT on the server keeps the transaction short and entirely server-side, which reduces the window for locking, blocking, and partial failures.
• Set-based batch work. Bulk updates, ETL-style transforms, and maintenance jobs are naturally expressed as SQL and should not be pulled row by row into the app tier.

A second, often decisive reason is security. You can grant a login EXECUTE on a procedure while it has no direct SELECT, INSERT, or DELETE rights on the underlying tables. Through ownership chaining the procedure runs against the tables, but the caller never touches them directly. That gives you a controlled surface: the app can only do what the procedures allow, and parameterised procedures also help keep queries safe from injection. See SQL security for how permissions and ownership chaining fit together.

A third reason is plan reuse. Procedure calls compile an execution plan that the engine caches and reuses across invocations, and because parameters are passed separately the plan is naturally parameterised. In practice modern engines cache plans for parameterised ad hoc queries too, so this is a smaller edge than it once was, but it is still real for complex statements.

There are genuine counter-arguments, and a good answer acknowledges them. Business logic in the application is usually easier to unit test, refactor, and reason about with modern tooling; SQL in procedures is harder to test in isolation. Procedures can drift outside source control unless you deliberately script and version them alongside the app. And T-SQL is not portable: logic written for SQL Server does not move to PostgreSQL or MySQL without a rewrite, whereas application code often does. Spreading business rules across both tiers also makes a system harder to follow.

The balanced view: put data-shaped work (batch operations, transactional writes, set-based transforms) and anything that needs a hard security boundary in procedures, and keep business rules and orchestration in the application where they are testable and portable. Treat procedures as a deliberate API to the database, check them into source control like any other code, and you get the benefits of both without the mess.

CREATE OR ALTER PROCEDURE dbo.PlaceOrder
    @CustomerId INT,
    @ProductId  INT,
    @Qty        INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;

    INSERT INTO dbo.Orders (CustomerId, OrderDate)
    VALUES (@CustomerId, SYSUTCDATETIME());

    DECLARE @OrderId INT = SCOPE_IDENTITY();

    INSERT INTO dbo.OrderLines (OrderId, ProductId, Qty)
    VALUES (@OrderId, @ProductId, @Qty);

    UPDATE dbo.Inventory
    SET OnHand = OnHand - @Qty
    WHERE ProductId = @ProductId;

    COMMIT;
END;

-- The app login can run the procedure but cannot touch the tables directly
GRANT EXECUTE ON dbo.PlaceOrder TO app_user;

-- No direct rights are given on the underlying tables:
-- REVOKE SELECT, INSERT, UPDATE, DELETE ON dbo.Orders    FROM app_user;
-- REVOKE SELECT, INSERT, UPDATE, DELETE ON dbo.Inventory FROM app_user;

Related: What is the difference between a stored procedure and a function? · How do you prevent SQL injection in dynamic SQL? · Why are set based queries preferred over cursors?

A cursor walks a result set one row at a time: fetch a row, run some logic, fetch the next, and repeat until the end. Each iteration carries per-row overhead (fetching, context switching, re-checking the loop) and the optimizer never gets to see the work as a whole. A set-based statement instead tells the engine what you want for the entire set and lets it choose the fastest plan, using indexes, parallelism and batch operators. On any real data volume the set-based version is usually many times faster.

The key mental shift is procedural versus declarative. Row-by-row code is procedural (often called RBAR, "row by agonizing row"). Set-based SQL is declarative: you describe the outcome and the query optimizer decides how to reach it. Because a cursor hides intent inside a loop, the engine cannot reorder, batch, or parallelize the work the way it can with a single statement.

• Performance: one statement scans and updates in bulk; a cursor re-does fetch and dispatch overhead for every row, and often holds locks longer.
• Optimization: the optimizer can pick an index seek, a merge or hash join, or a parallel plan for a set-based statement, but not for hand-written loop logic. See performance tuning and indexes.
• Readability: a rewritten UPDATE ... FROM or INSERT ... SELECT is shorter and states intent directly.

Most cursors can be rewritten. A per-row calculation becomes a single UPDATE with a join or a CASE expression; a running total or ranking becomes a window function such as ROW_NUMBER, RANK or SUM() OVER; loading derived rows becomes INSERT ... SELECT. If a cursor is the cause of a slow procedure, this is often the fix (see why is my query slow).

Cursors are not always wrong. Legitimate cases are genuinely row-by-row administrative tasks (looping over databases or tables to run maintenance), calling a stored procedure or sending a message once per row, or complex sequential logic where each row depends on the outcome of the previous one and no window function expresses it. Even then, prefer a set-based approach first and reach for a cursor only when the work truly cannot be expressed as a set.

DECLARE @id INT, @price DECIMAL(10,2);

DECLARE price_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT product_id, price FROM Products WHERE discontinued = 0;

OPEN price_cur;
FETCH NEXT FROM price_cur INTO @id, @price;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Products
    SET price = @price * 1.10
    WHERE product_id = @id;

    FETCH NEXT FROM price_cur INTO @id, @price;
END

CLOSE price_cur;
DEALLOCATE price_cur;

UPDATE Products
SET price = price * 1.10
WHERE discontinued = 0;

Related: When should you use a stored procedure instead of application code? · How do you use temp tables inside a stored procedure? · How do you troubleshoot a slow query?

A stored procedure has three separate channels for handing data back to the caller, and interviewers want to see that you do not confuse them. The RETURN value and OUTPUT parameters are the two that get mixed up most often, because both travel through the argument list rather than as query results.

The RETURN statement sends back exactly one integer and nothing else. By long-standing convention it is a status or exit code, not real data: 0 means success and any non-zero value signals a specific error or branch the procedure took. You capture it with the EXEC @rc = proc syntax. If you never write an explicit RETURN, the procedure still returns 0.

An OUTPUT parameter is the right tool when you need to hand back actual data. It can be any data type (int, varchar, decimal, datetime, and so on), and a procedure can have many of them. You mark the parameter with the OUTPUT keyword in the procedure definition and again on the argument when you call it, otherwise the changed value is thrown away.

• RETURN: one value, integer only, meant as a status or exit code.
• OUTPUT: many values allowed, any type, meant to carry real data.
• Result set: a plain SELECT inside the procedure streams back rows and columns, which is how you return a table of data rather than a single scalar.

A practical rule: use RETURN to signal whether the work succeeded, use OUTPUT to pass a computed scalar such as a new identity value or a running total, and use a SELECT result set when the answer is a set of rows. Pairing a non-zero RETURN code with a raised error (see error 201, procedure expects a parameter that was not supplied) is a common pattern, though modern code usually favours TRY...CATCH and THROW for real error handling.

CREATE PROCEDURE usp_TransferFunds
    @FromAccount INT,
    @Amount DECIMAL(12,2)
AS
BEGIN
    IF @Amount <= 0
        RETURN 1;          -- non-zero: invalid amount

    IF NOT EXISTS (SELECT 1 FROM Accounts WHERE account_id = @FromAccount)
        RETURN 2;          -- non-zero: account not found

    -- ... do the transfer ...
    RETURN 0;              -- 0: success
END;
GO

DECLARE @rc INT;
EXEC @rc = usp_TransferFunds @FromAccount = 10, @Amount = 250.00;
IF @rc = 0
    PRINT 'Success';
ELSE
    PRINT 'Failed with status ' + CAST(@rc AS VARCHAR(10));

CREATE PROCEDURE usp_AddCustomer
    @Name       VARCHAR(100),
    @NewId      INT           OUTPUT,
    @CreatedAt  DATETIME2     OUTPUT
AS
BEGIN
    INSERT INTO Customers (name) VALUES (@Name);
    SET @NewId     = SCOPE_IDENTITY();
    SET @CreatedAt = SYSDATETIME();
END;
GO

DECLARE @id INT, @when DATETIME2;
EXEC usp_AddCustomer
    @Name = 'Acme Ltd',
    @NewId = @id OUTPUT,        -- OUTPUT keyword required on the call too
    @CreatedAt = @when OUTPUT;
SELECT @id AS new_customer_id, @when AS created_at;

Related: How do input and output parameters work in a stored procedure? · What is the difference between a stored procedure and a function? · How do you handle errors in T-SQL with TRY CATCH?

When a stored procedure runs for the first time, SQL Server compiles an execution plan for it: the optimizer inspects the query, the parameter values it was called with, and the table statistics, then picks a strategy (which index to use, which join type, and so on). Compiling is not free, so the finished plan is stored in the plan cache in memory.

Every later call that matches the same cached plan reuses it and skips compilation entirely. This is the main performance win of stored procedures and parameterized queries: you pay the compile cost once and amortize it across thousands of executions.

The catch is parameter sniffing. The plan is built for whatever parameter values were supplied on that first compile. If those values were unusual (for example a customer with 2 rows) the cached plan can be a poor fit for a later call with very different data volume (a customer with 2 million rows), and the reused plan runs badly. The same happens when a plan goes stale after the data distribution shifts.

You can ask for a fresh plan in two ways. OPTION (RECOMPILE) on a single statement recompiles just that statement on every run, using the actual parameter values each time (great for a query with wildly varying inputs). WITH RECOMPILE on the procedure recompiles the whole proc on every call and never caches its plan - simpler but more expensive.

SQL Server also invalidates a cached plan on its own. A plan is marked for recompilation when relevant statistics change (enough rows modified to trip the auto-update threshold), when the underlying schema changes (adding or dropping an index or column), or when someone runs sp_recompile. See performance tuning for how this fits into diagnosing slow queries.

• Cache hit: plan found and reused - fast, no compile.
• Recompile: plan rebuilt for current parameters and stats - correct, but costs CPU.
• DBCC FREEPROCCACHE clears cached plans server wide. It is a blunt instrument for a dev or test box only; never run it casually in production because it forces every proc to recompile at once.

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT o.order_id, o.order_date, o.total
    FROM   dbo.Orders AS o
    WHERE  o.customer_id = @CustomerId
    OPTION (RECOMPILE);
END;

CREATE PROCEDURE dbo.RunAdHocReport
    @Region VARCHAR(20)
WITH RECOMPILE
AS
BEGIN
    SELECT r.region, SUM(s.amount) AS total_sales
    FROM   dbo.Sales AS s
    JOIN   dbo.Regions AS r ON r.region_id = s.region_id
    WHERE  r.region = @Region
    GROUP BY r.region;
END;

Related: What is parameter sniffing and how do you deal with it? · What are statistics and how does cardinality estimation affect plans? · When should you use a stored procedure instead of application code?

SQL Server supports nested transactions only in name. When you call BEGIN TRANSACTION inside an already open transaction, it does not start a genuinely separate unit of work. It simply increments the @@TRANCOUNT counter by one. The engine tracks a single physical transaction; the nesting level is just a number.

The consequences trip up a lot of people. A COMMIT only decrements @@TRANCOUNT by one, and the work is not durably committed until the count reaches zero, which happens on the outermost commit. So an inner COMMIT commits nothing on its own. A ROLLBACK TRANSACTION, by contrast, ignores the nesting entirely: it rolls back all open work and resets @@TRANCOUNT straight to zero. There is no such thing as rolling back only the inner transaction.

This creates a classic trap for stored procedures. If a proc issues its own BEGIN TRAN ... ROLLBACK while a caller already had a transaction open, the rollback wipes out the caller's work too, and because @@TRANCOUNT is now zero the caller's later COMMIT or ROLLBACK throws error 3902 or 3903 (a request to COMMIT/ROLLBACK with no corresponding BEGIN TRANSACTION). An inner ROLLBACK under an open outer transaction is effectively a landmine.

The robust pattern is to make each proc undo only its own work with a savepoint. SAVE TRANSACTION marks a point you can partially roll back to with ROLLBACK TRANSACTION savepoint_name, which does not touch @@TRANCOUNT and does not discard the caller's earlier work. The proc inspects @@TRANCOUNT on entry: if a transaction is already active it sets a savepoint instead of a fresh BEGIN TRAN, and on error rolls back only to that savepoint. If no transaction is active it manages its own from start to finish.

One caveat: a savepoint cannot rescue a transaction that has already been doomed, for example by a severe error inside TRY...CATCH where XACT_STATE() returns -1. In that case the only legal move is a full ROLLBACK. For the underlying guarantees these mechanics protect, see SQL transactions and ACID explained and the site guide on transactions, locks and deadlocks.

CREATE PROCEDURE dbo.AddOrderLine
    @OrderId INT,
    @ProductId INT,
    @Qty INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Remember whether the caller already opened a transaction.
    DECLARE @outerTran INT = @@TRANCOUNT;

    IF @outerTran > 0
        SAVE TRANSACTION AddOrderLine;   -- nested call: set a savepoint
    ELSE
        BEGIN TRANSACTION;               -- top level: own the transaction

    BEGIN TRY
        INSERT INTO dbo.OrderLines (OrderId, ProductId, Qty)
        VALUES (@OrderId, @ProductId, @Qty);

        UPDATE dbo.Inventory
        SET OnHand = OnHand - @Qty
        WHERE ProductId = @ProductId;

        IF @outerTran = 0
            COMMIT TRANSACTION;          -- only commit what we started
    END TRY
    BEGIN CATCH
        IF @outerTran > 0
            -- Undo just this proc's work; leave the caller's intact.
            ROLLBACK TRANSACTION AddOrderLine;
        ELSE IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;        -- we own it: full rollback

        THROW;                           -- let the caller decide next
    END CATCH
END;

-- @@TRANCOUNT tracks the nesting level, not separate transactions.
BEGIN TRANSACTION;              -- @@TRANCOUNT = 1
    INSERT INTO dbo.Audit (Msg) VALUES ('outer');

    BEGIN TRANSACTION;          -- @@TRANCOUNT = 2 (no new real transaction)
        INSERT INTO dbo.Audit (Msg) VALUES ('inner');
    COMMIT TRANSACTION;         -- @@TRANCOUNT = 1, still nothing committed

COMMIT TRANSACTION;             -- @@TRANCOUNT = 0, NOW both rows commit

-- Contrast: a ROLLBACK ignores nesting and undoes everything.
BEGIN TRANSACTION;              -- @@TRANCOUNT = 1
    INSERT INTO dbo.Audit (Msg) VALUES ('keep me');
    BEGIN TRANSACTION;          -- @@TRANCOUNT = 2
        ROLLBACK TRANSACTION;   -- @@TRANCOUNT = 0: 'keep me' is gone too
    COMMIT TRANSACTION;         -- error 3902: no matching BEGIN TRANSACTION

Related: How do you use a transaction inside a stored procedure? · How do you handle errors in T-SQL with TRY CATCH? · What is a transaction and what does ACID mean?

A local temporary table is written with a single hash prefix, for example #staging. When a stored procedure runs CREATE TABLE #staging, SQL Server physically creates the table in the tempdb database (with a unique internal name), not in your user database. It is a real table on disk pages, so it can hold a large staging result set without bloating your permanent schema.

• Scope and lifetime. A #temp table is visible to the procedure that created it and to any nested procedure it calls, but not to sibling sessions. It is dropped automatically when the creating procedure returns (or when the session ends), so you rarely need an explicit DROP TABLE.
• Statistics and indexes. Because it is a real table, the optimizer builds column statistics on it and you can add a clustered or nonclustered index, or a PRIMARY KEY. That lets the optimizer produce good plans when you join or filter a large intermediate set.
• Global temp tables. A double hash, ##shared, creates a global temp table that is visible to all sessions and lives until the creating session ends and no other session is still using it. Global temp tables are rarely the right choice inside a proc because of the cross-session sharing.

Compare the alternatives. A table variable (DECLARE @t TABLE (...)) also lives in tempdb but historically carries no real statistics (the optimizer assumes one row on older compatibility levels), cannot be altered or indexed as flexibly, and its scope is only the current batch or procedure. A CTE (WITH x AS (...)) is not stored at all - it is just an inline query name that exists for one statement and is re-evaluated each time it is referenced. Reach for a #temp table when you need to materialize a big intermediate result once and reuse or index it. See temp table vs table variable for a fuller contrast.

Recompilation note. Creating and populating a #temp table inside a proc, then querying it, can trigger statement-level recompilations because the temp table and its statistics did not exist when the plan was first compiled. This is usually a good thing (fresh statistics give a better plan), but on very hot, high-frequency procedures the recompile cost can matter. If it does, options include OPTION (RECOMPILE) on the specific statement, or the KEEPFIXED PLAN hint to suppress statistics-driven recompiles. Table variables avoid these recompiles, which is one reason people pick them for small sets.

A practical pattern: use a #temp table to stage a filtered, pre-aggregated slice of a big table once, index it, and then join it several times in the rest of the procedure instead of re-scanning the source table each time.

CREATE PROCEDURE dbo.usp_TopCustomerOrders
    @MinTotal money
AS
BEGIN
    SET NOCOUNT ON;

    -- Create the local temp table in tempdb
    CREATE TABLE #BigOrders
    (
        customer_id int      NOT NULL,
        order_id    int      NOT NULL,
        total       money    NOT NULL
    );

    -- Stage a filtered slice of a large table once
    INSERT INTO #BigOrders (customer_id, order_id, total)
    SELECT customer_id, order_id, total
    FROM   dbo.Orders
    WHERE  total >= @MinTotal;

    -- Reuse it: join back to Customers
    SELECT c.name, b.order_id, b.total
    FROM   #BigOrders AS b
    JOIN   dbo.Customers AS c
        ON c.customer_id = b.customer_id
    ORDER BY b.total DESC;

    -- No explicit DROP needed: #BigOrders is dropped when the proc returns
END;

CREATE TABLE #BigOrders
(
    customer_id int   NOT NULL,
    order_id    int   NOT NULL,
    total       money NOT NULL
);

INSERT INTO #BigOrders (customer_id, order_id, total)
SELECT customer_id, order_id, total
FROM   dbo.Orders
WHERE  total >= 1000;

-- Add a clustered index so later joins on customer_id seek instead of scan
CREATE CLUSTERED INDEX IX_BigOrders_Customer
    ON #BigOrders (customer_id);

Related: What is the difference between a temp table and a table variable? · Why are set based queries preferred over cursors? · How do you use a transaction inside a stored procedure?