CREATE OR ALTER PROCEDURE dbo.get_orders_by_region
    @region      varchar(50),
    @min_amount  decimal(10,2) = 0        -- default makes it optional
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT order_id, customer_id, amount, ordered_at
    FROM dbo.orders
    WHERE region = @region
      AND amount >= @min_amount;
END;

EXEC dbo.get_orders_by_region @region = 'EMEA', @min_amount = 100;

CREATE OR ALTER PROCEDURE dbo.create_customer
    @name         nvarchar(100),
    @customer_id  int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.customers (name) VALUES (@name);
    SET @customer_id = SCOPE_IDENTITY();
END;

DECLARE @id int;
EXEC dbo.create_customer @name = N'Acme GmbH', @customer_id = @id OUTPUT;
SELECT @id;

DECLARE @rc int;
EXEC @rc = dbo.some_procedure @param = 1;

CREATE TYPE dbo.order_line_list AS TABLE (
    product_id int           NOT NULL,
    quantity   int           NOT NULL,
    price      decimal(10,2) NOT NULL
);
GO
 
CREATE OR ALTER PROCEDURE dbo.add_order_lines
    @order_id int,
    @lines    dbo.order_line_list READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.order_lines (order_id, product_id, quantity, price)
    SELECT @order_id, product_id, quantity, price
    FROM @lines;
END;

-- First call: @region = 'ANTARCTICA' (5 rows) → plan with key lookups
EXEC dbo.get_orders_by_region @region = 'ANTARCTICA';
-- Later: @region = 'EMEA' (40 million rows) → same lookup plan, now catastrophic
EXEC dbo.get_orders_by_region @region = 'EMEA';

CREATE OR ALTER PROCEDURE dbo.transfer_stock
    @from_warehouse int, @to_warehouse int, @product_id int, @qty int
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRAN;
        UPDATE dbo.stock SET qty = qty - @qty
        WHERE warehouse_id = @from_warehouse AND product_id = @product_id;
        UPDATE dbo.stock SET qty = qty + @qty
        WHERE warehouse_id = @to_warehouse AND product_id = @product_id;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;   -- rethrows original error, preserves the real line number
    END CATCH
END;
 
Why this shape matters: without `XACT_ABORT ON`, many runtime errors abort only the current statement and leave the transaction half-done and still open. And a `CATCH` block that rolls back but swallows the error (no `THROW`) turns failures into silent no-ops. The full story is in our [transactions guide](/guides/mssql/transactions).
 
Procedures can call procedures up to 32 nesting levels deep (`@@NESTLEVEL` tells you where you are). If you're anywhere near that limit, the design has already gone wrong.
 
## When Not to Use a Stored Procedure
 
Honest scoping, because "put everything in procs" and "never use procs" are both dogma:
 
- **Single static queries an app runs**: parameterized queries from the app get the same plan caching. A procedure adds a deployment surface without adding value.
- **Business logic that changes weekly**: T-SQL is harder to test, version, and code-review than application code. Procedures earn their keep for set-based data logic close to the data, not for rules engines.
- **Dynamic search screens** ("filter by any combination of 12 fields"): a procedure with 12 optional parameters is a parameter-sniffing generator. Either build the SQL dynamically with `sp_executesql` and proper parameters, or accept `OPTION (RECOMPILE)`.
 
Where procedures genuinely win: multi-statement transactional units (the stock transfer above), batch operations over TVPs, a security boundary (grant `EXECUTE` on the proc, no table access), and keeping chatty multi-step logic on the server side of the network.
 
## Common Mistakes
 
| Mistake | Symptom | Fix |
|---|---|---|
| Forgetting `OUTPUT` at the call site | Output variable silently `NULL` | Write `OUTPUT` in both places |
| `sp_` prefix | Shadowed by/confused with system procs | Different prefix or schema-based naming |
| Missing `SET NOCOUNT ON` | ORM confusion, extra network messages | First line of the procedure |
| Using `RETURN` to pass data | Truncated to `int`, breaks conventions | `OUTPUT` parameter or result set |
| Local-variable copy of a parameter | Optimizer plans blind, hides intent | `OPTION (OPTIMIZE FOR ...)` explicitly |
| One giant proc with optional params | Random slowness (sniffing) | Recompile hint, or split per case |
| `CATCH` without `THROW` | Errors vanish, callers think it worked | Always rethrow unless genuinely handled |
 
Writing and iterating on procedure bodies means a lot of exploratory `SELECT`s against the tables involved; Mako's AI autocomplete is useful for sketching those queries before you freeze them into a procedure.
 
Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.