Creating SQL Server Stored Procedures

This document provides a comprehensive guide to creating stored procedures in SQL Server. Stored procedures are a powerful feature that allows you to encapsulate T-SQL statements into a single unit, improving performance, security, and maintainability of your database applications.

What are Stored Procedures?

A stored procedure is a precompiled collection of one or more Transact-SQL statements that are stored on the database server. They can accept input parameters, return output parameters, and perform a wide range of database operations, including complex logic, error handling, and transaction management.

Benefits of Using Stored Procedures

  • Performance: Stored procedures are compiled and cached by SQL Server, leading to faster execution compared to ad-hoc SQL queries.
  • Security: You can grant execute permissions on stored procedures without granting direct access to the underlying tables, enhancing data security.
  • Maintainability: Changes to database logic can be made within the stored procedure without modifying application code.
  • Reusability: Stored procedures can be called from multiple applications, reducing code duplication.
  • Reduced Network Traffic: Only the call to the stored procedure and its parameters are sent over the network, rather than the entire SQL statement.

Syntax for Creating a Stored Procedure

The basic syntax for creating a stored procedure using the CREATE PROCEDURE statement is as follows:

CREATE PROCEDURE procedure_name
                    [ @parameter1 datatype [ = default_value ],
                      @parameter2 datatype [ = default_value ]
                      [ OUTPUT ]
                    ]
                AS
                BEGIN
                    -- T-SQL statements go here
                    SELECT 'Hello, ' + @parameter1 AS Message;
                END;

Explanation of Syntax Elements:

  • CREATE PROCEDURE procedure_name: This statement initiates the creation of a new stored procedure with the specified name.
  • @parameter_name datatype: Defines an input or output parameter for the procedure. Parameters are optional.
  • OUTPUT: Specifies that the parameter is an output parameter, used to return a value from the procedure.
  • AS: Marks the beginning of the stored procedure's body.
  • BEGIN ... END: Encloses the block of T-SQL statements that constitute the procedure's logic.

Example: Creating a Simple Stored Procedure

Let's create a stored procedure that retrieves customer information based on a customer ID.

USE AdventureWorks2022; -- Assuming AdventureWorks database
                GO

                CREATE PROCEDURE dbo.usp_GetCustomerByID
                    @CustomerID INT
                AS
                BEGIN
                    -- SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client
                    -- for each statement in a stored procedure.
                    SET NOCOUNT ON;

                    SELECT
                        c.CustomerID,
                        p.FirstName,
                        p.LastName,
                        p.EmailAddress
                    FROM
                        Sales.Customer AS c
                    INNER JOIN
                        Person.Person AS p
                    ON c.PersonID = p.BusinessEntityID
                    WHERE
                        c.CustomerID = @CustomerID;
                END;
                GO

SET NOCOUNT ON; is a common practice at the beginning of stored procedures to improve performance by reducing network traffic. It suppresses the message indicating the number of rows affected by a T-SQL statement.

Creating Stored Procedures with Output Parameters

Output parameters are used to return single values from a stored procedure. Here's an example of a procedure that returns the total order count for a customer.

USE AdventureWorks2022;
                GO

                CREATE PROCEDURE dbo.usp_GetCustomerOrderCount
                    @CustomerID INT,
                    @OrderCount INT OUTPUT
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT @OrderCount = COUNT(*)
                    FROM Sales.SalesOrderHeader
                    WHERE CustomerID = @CustomerID;
                END;
                GO

Creating Stored Procedures with Default Parameter Values

You can define default values for parameters, making them optional when calling the procedure.

USE AdventureWorks2022;
                GO

                CREATE PROCEDURE dbo.usp_FindProductsByCategory
                    @CategoryID INT = 1 -- Default to CategoryID 1 (Bikes)
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT
                        p.ProductID,
                        p.Name,
                        p.ListPrice
                    FROM
                        Production.Product AS p
                    INNER JOIN
                        Production.ProductCategory AS pc
                    ON p.ProductSubcategoryID = pc.ProductSubcategoryID -- Simplified join for example
                    WHERE
                        pc.ProductCategoryID = @CategoryID;
                END;
                GO

When creating stored procedures, ensure proper naming conventions (e.g., using prefixes like usp_) for better organization and clarity.

Further Reading