Stored Procedures Best Practices

Stored Procedures Best Practices

Stored procedures are pre-compiled T-SQL batches stored in the database. They offer performance benefits (cached execution plans), security (execute-only permissions), and maintainability.

Creating a Stored Procedure

CREATE PROCEDURE usp_GetEmployeesByDept
    @DeptID int,
    @ActiveOnly bit = 1
AS
BEGIN
    SET NOCOUNT ON

    SELECT EmployeeID, FirstName, LastName, Email, HireDate, Salary
    FROM Employees
    WHERE DeptID = @DeptID
      AND (@ActiveOnly = 0 OR IsActive = 1)
    ORDER BY LastName, FirstName
END
GO

-- Execute the procedure
EXEC usp_GetEmployeesByDept @DeptID = 3
EXEC usp_GetEmployeesByDept @DeptID = 3, @ActiveOnly = 0

Output Parameters

CREATE PROCEDURE usp_InsertEmployee
    @FirstName  varchar(50),
    @LastName   varchar(50),
    @Email      varchar(100),
    @DeptID     int,
    @NewID      int OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO Employees (FirstName, LastName, Email, DeptID)
    VALUES (@FirstName, @LastName, @Email, @DeptID)

    SET @NewID = SCOPE_IDENTITY()
END
GO

-- Call with output parameter
DECLARE @ID int
EXEC usp_InsertEmployee 'Jane', 'Doe', 'jdoe@flamenet.io', 2, @ID OUTPUT
PRINT 'New Employee ID: ' + CAST(@ID AS varchar)

Best Practices

• Use SET NOCOUNT ON: Prevents "n rows affected" messages from being sent to the client, reducing network traffic.
• Use SCOPE_IDENTITY(): Instead of @@IDENTITY, which can return the wrong value if a trigger inserts into another table with an identity column.
• Prefix with usp_: Avoid the sp_ prefix. SQL Server checks the master database first for sp_ procedures, adding overhead.
• Error handling: Check @@ERROR after each statement, or use TRY...CATCH in SQL Server 2005+.
• Avoid SELECT *: List specific columns to avoid breaking the procedure if columns are added or reordered.
• Use schema prefixes: dbo.usp_GetEmployees avoids recompilation caused by schema resolution.

Error Handling Pattern

CREATE PROCEDURE usp_TransferFunds
    @FromAcct int, @ToAcct int, @Amount money
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION

    UPDATE Accounts SET Balance = Balance - @Amount WHERE AcctID = @FromAcct
    IF @@ERROR <> 0 GOTO RollbackTran

    UPDATE Accounts SET Balance = Balance + @Amount WHERE AcctID = @ToAcct
    IF @@ERROR <> 0 GOTO RollbackTran

    COMMIT TRANSACTION
    RETURN 0

RollbackTran:
    ROLLBACK TRANSACTION
    RETURN -1
END