SQL Stored Procedure Tutorial: Creating Procedures and Best Practices [Complete Guide]

Stored Procedures are one of the most powerful features in SQL Server, allowing you to encapsulate complex SQL logic into reusable program units. This article will start from basic syntax, covering parameter usage, error handling, performance optimization, and help you master stored procedure development techniques through practical examples.

Stored Procedure Concepts and Benefits

A stored procedure is a collection of pre-compiled SQL statements stored in the database that can be executed by calling its name.

What is a Stored Procedure?

Simply put, a stored procedure is like a "function" or "subroutine" within the database:

-- Calling a stored procedure (as simple as calling a function)
EXEC GetEmployeeById @EmployeeId = 1001;

Benefits of Using Stored Procedures

Benefit	Description
Performance Improvement	Pre-compiled execution plan reduces compilation overhead for each execution
Code Reuse	Write once, call from multiple places, avoiding duplicate code
Enhanced Security	Users only need execute permission, no direct table access required
Easy Maintenance	Centralized logic management, modify once and apply everywhere
Reduced Network Traffic	Only pass parameters and results, no need to send complete SQL statements
Transaction Control	Complete transaction logic can be implemented within procedures

Stored Procedure vs Direct SQL Execution

-- Method 1: Direct SQL execution (send complete statement each time)
SELECT e.Name, e.Email, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentId = d.Id
WHERE e.Status = 'Active' AND d.Location = 'Taipei';

-- Method 2: Call stored procedure (only pass parameters, more secure and efficient)
EXEC GetActiveEmployeesByLocation @Location = 'Taipei';

Want to learn more about advanced SQL features? See SQL Advanced Techniques Practical Tips.

CREATE PROCEDURE Syntax

Basic Syntax Structure

The basic syntax for creating a stored procedure:

CREATE PROCEDURE procedure_name
    @Parameter1 data_type,
    @Parameter2 data_type = default_value
AS
BEGIN
    SET NOCOUNT ON;

    -- SQL statements
    SELECT * FROM TableName WHERE Column = @Parameter1;
END
GO

Complete Example: Creating Your First Stored Procedure

-- Create a stored procedure to get employee data
CREATE PROCEDURE usp_GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        EmployeeId,
        Name,
        Email,
        Department,
        HireDate
    FROM Employees
    WHERE EmployeeId = @EmployeeId;
END
GO

-- Execute the stored procedure
EXEC usp_GetEmployeeById @EmployeeId = 1001;

Naming Conventions

Good naming conventions help with code maintenance:

Prefix	Purpose	Example
usp_	User Stored Procedure	usp_GetEmployee
sp_	Avoid using (system reserved)	-
rpt_	Report-related procedures	rpt_MonthlySales
etl_	Data processing procedures	etl_ImportOrders

Important Note: Avoid the sp_ prefix as SQL Server searches the master database first, affecting performance.

Modifying and Deleting Stored Procedures

-- Modify an existing stored procedure
ALTER PROCEDURE usp_GetEmployeeById
    @EmployeeId INT,
    @IncludeInactive BIT = 0  -- New parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Email, Department
    FROM Employees
    WHERE EmployeeId = @EmployeeId
      AND (@IncludeInactive = 1 OR Status = 'Active');
END
GO

-- Delete a stored procedure
DROP PROCEDURE IF EXISTS usp_GetEmployeeById;

Parameter Usage

Stored procedures support input parameters, output parameters, and default values, providing flexible data passing methods.

INPUT Parameters

Input parameters are used to pass data into the stored procedure:

CREATE PROCEDURE usp_SearchEmployees
    @Department NVARCHAR(50),
    @MinSalary DECIMAL(10,2),
    @MaxSalary DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Department, Salary
    FROM Employees
    WHERE Department = @Department
      AND Salary BETWEEN @MinSalary AND @MaxSalary
    ORDER BY Salary DESC;
END
GO

-- Calling method
EXEC usp_SearchEmployees
    @Department = 'Engineering',
    @MinSalary = 50000,
    @MaxSalary = 100000;

OUTPUT Parameters

Output parameters are used to return data from the stored procedure:

CREATE PROCEDURE usp_GetEmployeeCount
    @Department NVARCHAR(50),
    @EmployeeCount INT OUTPUT,
    @AverageSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @EmployeeCount = COUNT(*),
        @AverageSalary = AVG(Salary)
    FROM Employees
    WHERE Department = @Department;
END
GO

-- Call and receive output values
DECLARE @Count INT, @AvgSalary DECIMAL(10,2);

EXEC usp_GetEmployeeCount
    @Department = 'Engineering',
    @EmployeeCount = @Count OUTPUT,
    @AverageSalary = @AvgSalary OUTPUT;

SELECT @Count AS EmployeeCount, @AvgSalary AS AverageSalary;

Default Value Settings

Set default values for parameters to make them optional:

CREATE PROCEDURE usp_GetEmployees
    @Department NVARCHAR(50) = NULL,      -- Optional, default NULL
    @Status NVARCHAR(20) = 'Active',      -- Optional, default Active
    @PageSize INT = 50,                   -- Optional, default 50
    @PageNumber INT = 1                   -- Optional, default 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Department, Status
    FROM Employees
    WHERE (@Department IS NULL OR Department = @Department)
      AND Status = @Status
    ORDER BY EmployeeId
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END
GO

-- Call using default values
EXEC usp_GetEmployees;

-- Override some parameters
EXEC usp_GetEmployees @Department = 'Sales', @PageSize = 100;

Error Handling

Comprehensive error handling is a key element of enterprise-grade stored procedures.

TRY CATCH Block

SQL Server provides structured error handling mechanism:

CREATE PROCEDURE usp_TransferFunds
    @FromAccountId INT,
    @ToAccountId INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Debit
        UPDATE Accounts
        SET Balance = Balance - @Amount
        WHERE AccountId = @FromAccountId;

        -- Check balance
        IF (SELECT Balance FROM Accounts WHERE AccountId = @FromAccountId) < 0
        BEGIN
            THROW 50001, 'Insufficient balance, transfer cannot be completed.', 1;
        END

        -- Credit
        UPDATE Accounts
        SET Balance = Balance + @Amount
        WHERE AccountId = @ToAccountId;

        COMMIT TRANSACTION;

        SELECT 'SUCCESS' AS Status, 'Transfer successful' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            'ERROR' AS Status,
            ERROR_MESSAGE() AS Message,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_LINE() AS ErrorLine;
    END CATCH
END
GO

Error Information Functions

The following functions can be used in CATCH blocks to get error details:

Function	Description
ERROR_NUMBER()	Error code
ERROR_MESSAGE()	Error message
ERROR_SEVERITY()	Severity level
ERROR_STATE()	Error state
ERROR_LINE()	Line number where error occurred
ERROR_PROCEDURE()	Procedure name where error occurred

RAISERROR vs THROW

Comparison of two ways to raise errors:

-- RAISERROR (older method)
RAISERROR('Custom error message', 16, 1);

-- THROW (recommended for SQL Server 2012+)
THROW 50001, 'Custom error message', 1;

Comparison:

Feature	RAISERROR	THROW
Version Support	All versions	SQL Server 2012+
Syntax	Requires severity	Simpler
Statement Ending	No semicolon needed	Needs semicolon before
Re-throw	Must reconstruct	Just use THROW;

Transaction Rollback Best Practices

CREATE PROCEDURE usp_ProcessOrder
    @OrderId INT,
    @Result NVARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Auto-rollback on error

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Update order status
        UPDATE Orders SET Status = 'Processing' WHERE OrderId = @OrderId;

        -- Deduct inventory
        UPDATE Inventory
        SET Quantity = Quantity - oi.Quantity
        FROM Inventory i
        JOIN OrderItems oi ON i.ProductId = oi.ProductId
        WHERE oi.OrderId = @OrderId;

        -- Log transaction
        INSERT INTO TransactionLog (OrderId, Action, Timestamp)
        VALUES (@OrderId, 'Order Processed', GETDATE());

        COMMIT TRANSACTION;
        SET @Result = 'SUCCESS';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @Result = 'ERROR: ' + ERROR_MESSAGE();

        -- Log error
        INSERT INTO ErrorLog (ProcedureName, ErrorMessage, Timestamp)
        VALUES ('usp_ProcessOrder', ERROR_MESSAGE(), GETDATE());
    END CATCH
END
GO

Performance Optimization Tips

Performance optimization of stored procedures is crucial for overall system performance.

SET NOCOUNT ON

Add this setting at the beginning of every stored procedure:

CREATE PROCEDURE usp_Example
AS
BEGIN
    SET NOCOUNT ON;  -- Don't return affected row count

    -- Your SQL logic
END

Benefit: Reduces network traffic by avoiding unnecessary "xx rows affected" messages.

Avoiding Parameter Sniffing Issues

Parameter Sniffing can cause execution plans that don't work well for certain parameter values:

-- Problem scenario: First execution parameter value affects all subsequent executions
CREATE PROCEDURE usp_GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Method 1: Use local variables
    DECLARE @LocalStart DATE = @StartDate;
    DECLARE @LocalEnd DATE = @EndDate;

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate BETWEEN @LocalStart AND @LocalEnd;
END
GO

-- Method 2: Use OPTION (RECOMPILE)
CREATE PROCEDURE usp_GetOrdersByDate_V2
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (RECOMPILE);  -- Recompile each time
END
GO

Proper Use of Temporary Tables

Temporary tables can improve performance when processing large amounts of data:

CREATE PROCEDURE usp_GenerateReport
    @Year INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Use temp table to store intermediate results
    CREATE TABLE #MonthlySales (
        Month INT,
        TotalSales DECIMAL(18,2),
        OrderCount INT
    );

    -- Populate data
    INSERT INTO #MonthlySales
    SELECT
        MONTH(OrderDate),
        SUM(TotalAmount),
        COUNT(*)
    FROM Orders
    WHERE YEAR(OrderDate) = @Year
    GROUP BY MONTH(OrderDate);

    -- Use temp table for subsequent calculations
    SELECT
        m.Month,
        m.TotalSales,
        m.OrderCount,
        m.TotalSales / NULLIF(m.OrderCount, 0) AS AvgOrderValue
    FROM #MonthlySales m
    ORDER BY m.Month;

    -- Temp table is automatically dropped when procedure ends
END
GO

Other Performance Tips

1. Avoid using functions on columns in WHERE clause

   -- Bad: Cannot use index
   WHERE YEAR(OrderDate) = 2024
   
   -- Good: Can use index
   WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
   

2. Use EXISTS instead of IN (when handling large subqueries)

3. Limit returned columns (avoid SELECT *)

For more performance optimization techniques, see SQL Performance Tuning Complete Guide.

Practical Example: Report Generator

Here's a complete report generator stored procedure example:

CREATE PROCEDURE usp_GenerateSalesReport
    @StartDate DATE,
    @EndDate DATE,
    @Department NVARCHAR(50) = NULL,
    @MinAmount DECIMAL(18,2) = 0,
    @TotalSales DECIMAL(18,2) OUTPUT,
    @TotalOrders INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Validate parameters
        IF @StartDate > @EndDate
        BEGIN
            THROW 50001, 'Start date cannot be greater than end date', 1;
        END

        -- Create temp table to store report data
        CREATE TABLE #ReportData (
            OrderId INT,
            OrderDate DATE,
            CustomerName NVARCHAR(100),
            Department NVARCHAR(50),
            TotalAmount DECIMAL(18,2),
            ItemCount INT
        );

        -- Populate report data
        INSERT INTO #ReportData
        SELECT
            o.OrderId,
            o.OrderDate,
            c.CustomerName,
            e.Department,
            o.TotalAmount,
            (SELECT COUNT(*) FROM OrderItems WHERE OrderId = o.OrderId)
        FROM Orders o
        JOIN Customers c ON o.CustomerId = c.CustomerId
        JOIN Employees e ON o.SalesRepId = e.EmployeeId
        WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
          AND o.TotalAmount >= @MinAmount
          AND (@Department IS NULL OR e.Department = @Department);

        -- Calculate statistics
        SELECT
            @TotalSales = ISNULL(SUM(TotalAmount), 0),
            @TotalOrders = COUNT(*)
        FROM #ReportData;

        -- Return detail data
        SELECT
            OrderId,
            OrderDate,
            CustomerName,
            Department,
            TotalAmount,
            ItemCount
        FROM #ReportData
        ORDER BY OrderDate DESC, TotalAmount DESC;

        -- Return department summary
        SELECT
            Department,
            COUNT(*) AS OrderCount,
            SUM(TotalAmount) AS DepartmentTotal,
            AVG(TotalAmount) AS AvgOrderValue
        FROM #ReportData
        GROUP BY Department
        ORDER BY DepartmentTotal DESC;

    END TRY
    BEGIN CATCH
        SET @TotalSales = 0;
        SET @TotalOrders = 0;

        SELECT
            'ERROR' AS Status,
            ERROR_MESSAGE() AS Message;
    END CATCH
END
GO

-- Usage example
DECLARE @Sales DECIMAL(18,2), @Orders INT;

EXEC usp_GenerateSalesReport
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31',
    @Department = 'Sales',
    @MinAmount = 1000,
    @TotalSales = @Sales OUTPUT,
    @TotalOrders = @Orders OUTPUT;

SELECT @Sales AS TotalSales, @Orders AS TotalOrders;

Common Issues and Debugging

Debugging Techniques

1. Use PRINT to output debug messages

   PRINT 'Currently processing OrderId: ' + CAST(@OrderId AS VARCHAR(10));
   

2. View execution plan

   SET STATISTICS IO ON;
   SET STATISTICS TIME ON;
   EXEC usp_YourProcedure @Param = 'value';
   

3. Use SQL Server Profiler for tracing

Common Error Troubleshooting

Error	Possible Cause	Solution
Permission denied	Missing EXECUTE permission	GRANT EXECUTE ON usp_xxx TO UserRole
Parameter type mismatch	Input value doesn't match declared type	Check parameter data types
Object doesn't exist	Table or column name error	Verify object names and structure
Transaction not closed	TRY/CATCH not handling correctly	Ensure ROLLBACK is in CATCH block

Conclusion

Stored procedures are an indispensable tool in SQL Server development. Proper use of stored procedures can:

1. ✅ Improve application performance
2. ✅ Enhance database security
3. ✅ Simplify code maintenance
4. ✅ Implement complex business logic
5. ✅ Ensure transaction consistency

By mastering the syntax, parameter usage, error handling, and performance optimization techniques introduced in this article, you'll be able to develop high-quality enterprise-grade stored procedures.

Want to learn advanced query techniques? See SQL Window Functions Tutorial.

---

Want to improve your team's SQL development skills?

CloudInsight offers professional advanced SQL development training courses, covering:

• Stored procedure design patterns and best practices
• Advanced error handling and transaction control
• Performance tuning and execution plan analysis
• Practical project exercises and code reviews

Learn About Enterprise Training Programs →

---

FAQ

What's the difference between stored procedures and functions?

The main differences between Stored Procedures and User-Defined Functions:

Feature	Stored Procedure	Function
Return Value	Optional, via OUTPUT parameters or result sets	Must return a value
Use in SELECT	Cannot	Can (scalar functions)
Modify Data	Can (INSERT/UPDATE/DELETE)	Cannot
Transaction Control	Can use	Cannot
Error Handling	Full TRY CATCH support	More limited

Selection Guide: Use stored procedures when you need to modify data or execute complex logic; use functions when you need to calculate values within queries.

Do stored procedures automatically use indexes?

Stored procedures themselves don't "create" indexes, but they automatically use appropriate indexes that already exist on tables. SQL Server's query optimizer selects the best execution plan (including index usage strategy) based on statistics when compiling stored procedures.

Tips for ensuring index usage:

1. Ensure relevant columns have indexes created
2. Avoid using functions on indexed columns (e.g., WHERE YEAR(Date) = 2024)
3. Regularly update statistics (UPDATE STATISTICS)
4. Use SSMS to view execution plans and confirm index usage

---

Further Reading

• SQL Advanced Techniques Practical Tips
• SQL Performance Tuning Complete Guide
• SQL Window Functions Tutorial
• SQL Functions Complete Guide
• SSMS Complete Tutorial

---

References

1. Microsoft SQL Server Official Documentation - CREATE PROCEDURE
2. SQL Server Stored Procedure Best Practices Guide
3. SQL Server Error Handling Patterns