SQL Server Stored Procedures - MSDN Documentation

Stored Procedures in SQL Server

Stored procedures are a set of one or more Transact-SQL statements that are compiled and stored on the database server. They can accept input parameters and return multiple values in the form of result sets, output parameters, or return values. Stored procedures offer significant advantages in terms of performance, security, and maintainability for database applications.

This document provides a comprehensive guide to understanding, creating, and utilizing stored procedures in Microsoft SQL Server.

Creating Stored Procedures

You can create a stored procedure using the CREATE PROCEDURE or CREATE PROC statement. The basic syntax is as follows:


CREATE PROCEDURE procedure_name
    [ @parameter1 datatype [ = default_value ] [ OUTPUT ]
    , @parameter2 datatype [ = default_value ] [ OUTPUT ]
    , ... ]
AS
BEGIN
    -- Transact-SQL statements
    SELECT column1, column2
    FROM your_table
    WHERE some_column = @parameter1;

    -- Other operations like INSERT, UPDATE, DELETE can also be included
END
GO

Key components:

CREATE PROCEDURE: Keyword to start the procedure definition.
procedure_name: A unique name for your stored procedure.
@parameter_name: Optional parameters, prefixed with '@'.
datatype: The data type of the parameter (e.g., INT, VARCHAR(50), DATETIME).
= default_value: An optional default value for the parameter if none is provided during execution.
OUTPUT: Indicates that the parameter is an output parameter, returning a value back to the caller.
AS: Separates the procedure signature from the body.
BEGIN...END: Encloses the Transact-SQL statements that form the procedure's logic.
GO: A batch separator, not part of the T-SQL itself.

Executing Stored Procedures

Stored procedures are executed using the EXECUTE or EXEC command.


EXECUTE procedure_name [ @parameter1 = value1, @parameter2 = value2, ... ];
-- or
EXEC procedure_name value1, value2, ...; -- If parameters are in the defined order

You can also execute a procedure and capture its return value or output parameters.

Parameters

Stored procedures can use various types of parameters to make them flexible and reusable.

Input Parameters

These parameters pass values into the stored procedure. They are the most common type.


CREATE PROCEDURE GetCustomerById
    @CustomerID INT
AS
BEGIN
    SELECT *
    FROM Customers
    WHERE CustomerID = @CustomerID;
END
GO

EXEC GetCustomerById @CustomerID = 10;

Output Parameters

These parameters are used to return a single value from the stored procedure back to the caller. The OUTPUT keyword is crucial.


CREATE PROCEDURE GetCustomerCountByCity
    @CityName VARCHAR(50),
    @CustomerCount INT OUTPUT
AS
BEGIN
    SELECT @CustomerCount = COUNT(*)
    FROM Customers
    WHERE City = @CityName;
END
GO

DECLARE @Count INT;
EXEC GetCustomerCountByCity @CityName = 'London', @CustomerCount = @Count OUTPUT;
SELECT @Count AS NumberOfCustomersInLondon;

Return Values

Stored procedures can also return an integer status code using the RETURN statement. This is typically used to indicate success or failure, or specific status conditions.


CREATE PROCEDURE AddProduct
    @ProductName VARCHAR(100),
    @Price DECIMAL(10, 2)
AS
BEGIN
    IF EXISTS (SELECT * FROM Products WHERE ProductName = @ProductName)
    BEGIN
        RETURN 1; -- Error: Product already exists
    END
    ELSE
    BEGIN
        INSERT INTO Products (ProductName, Price) VALUES (@ProductName, @Price);
        RETURN 0; -- Success
    END
END
GO

DECLARE @Result INT;
EXEC @Result = AddProduct @ProductName = 'Gadget', @Price = 29.99;
IF @Result = 0
    PRINT 'Product added successfully.';
ELSE
    PRINT 'Product already exists.';

Benefits of Stored Procedures

Performance Improvement: Procedures are compiled once and stored in memory, reducing parsing and optimization overhead.
Reduced Network Traffic: Instead of sending multiple SQL statements over the network, you send a single EXECUTE command.
Enhanced Security: Permissions can be granted on stored procedures rather than directly on the underlying tables. This allows for controlled access to data without giving users direct table access.
Code Reusability: Complex logic can be encapsulated in a procedure and called from multiple applications or scripts.
Maintainability: Changes to database logic can be made in one place (the stored procedure) without modifying multiple application code files.
Consistency: Ensures that operations are performed in a standardized way across different applications.

Best Practices for Stored Procedures

Use Meaningful Names: Choose descriptive names for procedures and parameters.
Keep Procedures Focused: Each procedure should perform a single, well-defined task.
Validate Input: Always validate input parameters to prevent errors and security vulnerabilities.
Handle Errors Gracefully: Use TRY...CATCH blocks for robust error handling.
Avoid Cursors When Possible: Set-based operations are generally more efficient than row-by-row processing with cursors.
Use Appropriate Data Types: Match parameter and variable data types to the columns they interact with.
Minimize Dynamic SQL: If dynamic SQL is necessary, ensure it is properly parameterized and sanitized to prevent SQL injection.
Add Comments: Explain complex logic or non-obvious steps within the procedure.
Use SET NOCOUNT ON: This prevents the "x rows affected" messages from being returned, which can improve performance and reduce network traffic, especially when called from applications.


CREATE PROCEDURE GetOrderDetails
    @OrderID INT
AS
BEGIN
    SET NOCOUNT ON; -- Suppress row count messages

    BEGIN TRY
        SELECT
            od.ProductID,
            p.ProductName,
            od.Quantity,
            od.UnitPrice
        FROM OrderDetails AS od
        JOIN Products AS p ON od.ProductID = p.ProductID
        WHERE od.OrderID = @OrderID;

        IF @@ROWCOUNT = 0
        BEGIN
            -- Handle case where OrderID doesn't exist or has no details
            PRINT 'No details found for the specified OrderID.';
            RETURN -1; -- Custom return code for 'not found'
        END

        RETURN 0; -- Success
    END TRY
    BEGIN CATCH
        -- Log the error or handle it as appropriate
        PRINT 'An error occurred while retrieving order details.';
        -- Consider using THROW or RAISERROR for more detailed error reporting
        RETURN -2; -- Custom return code for 'error'
    END CATCH
END
GO

Common Stored Procedure Examples

Example 1: Get All Products


CREATE PROCEDURE GetAllProducts
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ProductID, ProductName, UnitPrice
    FROM Products
    ORDER BY ProductName;
END
GO

EXEC GetAllProducts;

Example 2: Add a New Customer


CREATE PROCEDURE AddNewCustomer
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Email VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT * FROM Customers WHERE Email = @Email)
    BEGIN
        RAISERROR('Customer with this email already exists.', 16, 1);
        RETURN 1;
    END
    ELSE
    BEGIN
        INSERT INTO Customers (FirstName, LastName, Email, CreatedDate)
        VALUES (@FirstName, @LastName, @Email, GETDATE());
        PRINT 'Customer added successfully.';
        RETURN 0;
    END
END
GO

EXEC AddNewCustomer @FirstName = 'Jane', @LastName = 'Doe', @Email = 'jane.doe@example.com';

Table of Contents