Stored procedures and functions encapsulate T-SQL on the server — reducing network round trips, centralizing validation, and controlling permissions. Balance database logic with application-layer testability.

Stored Procedure Basics 

  CREATE OR ALTER PROCEDURE dbo.GetUserOrders
    @UserID INT,
    @Status NVARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, Total, Status, CreatedAt
    FROM dbo.Orders
    WHERE UserID = @UserID
      AND (@Status IS NULL OR Status = @Status)
    ORDER BY CreatedAt DESC;
END;
GO

EXEC dbo.GetUserOrders @UserID = 42;
EXEC dbo.GetUserOrders @UserID = 42, @Status = N'Paid';

Always SET NOCOUNT ON in procedures — prevents extra record count messages to clients.

Output Parameters 

  CREATE OR ALTER PROCEDURE dbo.CountActiveUsers
    @Total INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Total = COUNT(*) FROM dbo.Users WHERE Status = N'active';
END;
GO

DECLARE @count INT;
EXEC dbo.CountActiveUsers @Total = @count OUTPUT;
SELECT @count AS active_users;

Prefer result sets over OUTPUT for application APIs — easier mapping in ORMs.

Scalar Functions 

  CREATE OR ALTER FUNCTION dbo.fn_OrderTotal (@OrderID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @total DECIMAL(18,2);
    SELECT @total = SUM(Quantity * UnitPrice)
    FROM dbo.OrderItems
    WHERE OrderID = @OrderID;
    RETURN ISNULL(@total, 0);
END;
GO

SELECT OrderID, dbo.fn_OrderTotal(OrderID) AS total FROM dbo.Orders;

Scalar UDFs in SELECT historically caused serial plans (pre-2019). Prefer inline table-valued functions or compute in JOIN.

Inline Table-Valued Functions 

  CREATE OR ALTER FUNCTION dbo.fn_ActiveUsersInCountry (@Country NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT UserID, Name, Email
    FROM dbo.Users
    WHERE Status = N'active' AND Country = @Country
);
GO

SELECT * FROM dbo.fn_ActiveUsersInCountry(N'US');

Inline TVFs merge into calling query — often optimize like views.

Multi-Statement TVFs 

  CREATE OR ALTER FUNCTION dbo.fn_TopCustomers (@MinOrders INT)
RETURNS @result TABLE (CustomerID INT, OrderCount INT)
AS
BEGIN
    INSERT INTO @result
    SELECT CustomerID, COUNT(*)
    FROM dbo.Orders
    GROUP BY CustomerID
    HAVING COUNT(*) >= @MinOrders;
    RETURN;
END;

Multi-statement TVFs have fixed cardinality estimates — use inline TVFs when possible.

Error Handling with TRY/CATCH 

  CREATE OR ALTER PROCEDURE dbo.SafeTransfer
    @FromAccountID INT,
    @ToAccountID INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Accounts SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccountID AND Balance >= @Amount;

        IF @@ROWCOUNT = 0
            THROW 50001, N'Insufficient funds or account not found', 1;

        UPDATE dbo.Accounts SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccountID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

SET XACT_ABORT ON rolls back on any error — consistent transaction state.

Parameter Sniffing

SQL Server caches execution plans based on first parameter values — skewed data causes bad plans:

  -- Option 1: RECOMPILE at procedure level
CREATE OR ALTER PROCEDURE dbo.SearchOrders @Status NVARCHAR(20)
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM dbo.Orders WHERE Status = @Status;
END;

-- Option 2: Statement-level hint
SELECT * FROM dbo.Orders WHERE Status = @Status OPTION (RECOMPILE);

-- Option 3: Optimize for unknown (legacy workaround)
CREATE OR ALTER PROCEDURE dbo.SearchOrders2 @Status NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM dbo.Orders WHERE Status = @Status
    OPTION (OPTIMIZE FOR (@Status UNKNOWN));
END;

Use Query Store to detect plan regressions after deployment.

Dynamic SQL in Procedures 

  CREATE OR ALTER PROCEDURE dbo.SearchProducts
    @NamePattern NVARCHAR(100),
    @MinPrice DECIMAL(10,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT ProductID, Name, Price
        FROM dbo.Products
        WHERE Name LIKE @NamePattern';

    IF @MinPrice IS NOT NULL
        SET @sql = @sql + N' AND Price >= @MinPrice';

    EXEC sp_executesql @sql,
        N'@NamePattern NVARCHAR(100), @MinPrice DECIMAL(10,2)',
        @NamePattern, @MinPrice;
END;

Never concatenate user input into SQL strings.

Triggers (Use Carefully) 

  CREATE OR ALTER TRIGGER dbo.tr_Orders_Audit
ON dbo.Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrdersAudit (OrderID, Action, ChangedAt)
    SELECT OrderID, N'INSERT', SYSUTCDATETIME() FROM inserted;
END;

Triggers hide logic — hard to debug, surprise performance cost. Prefer temporal tables or explicit audit in procedures.

Permissions 

  GRANT EXECUTE ON dbo.GetUserOrders TO app_api;
DENY SELECT ON dbo.Orders TO app_api;  -- Force access through proc

Procedure execution with ownership chaining can bypass granular table checks — document security model.

Common Mistakes

Mistake Impact
Business logic only in procs Hard to unit test
SELECT * in procs Schema breaks clients
Missing error handling Partial commits
Overusing scalar UDFs Serial plans, slow reports
No schema prefix dbo. Wrong object resolved

Performance Tips

  • Keep procedures set-based — no cursors on large sets
  • Create covering indexes for procedure queries
  • Use OPTION (MAXDOP 1) for small point queries if parallelism hurts
  • Monitor with sys.dm_exec_procedure_stats
  SELECT
    OBJECT_NAME(object_id) AS proc_name,
    execution_count,
    total_elapsed_time / execution_count AS avg_elapsed_us,
    last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY total_elapsed_time DESC;

Production Scenario: Pagination Procedure 

  CREATE OR ALTER PROCEDURE dbo.GetOrdersPage
    @PageSize INT = 20,
    @LastOrderID INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@PageSize)
        OrderID, CustomerID, Total, CreatedAt
    FROM dbo.Orders
    WHERE OrderID > @LastOrderID
    ORDER BY OrderID;
END;

Keyset pagination scales for infinite scroll APIs.

When to Keep Logic in Application Code

  • Complex business rules with frequent changes
  • Logic requiring external APIs
  • Heavy unit test coverage needs

Use procedures for data proximity, batch ETL, security boundaries, and stable high-volume queries.

Procedures centralize T-SQL — pair them with Query Store monitoring and version-controlled deployments for production reliability.