MSDN SQL Documentation

What are Stored Procedures?

Imagine you have a common task, like fetching a customer's order history, or updating product inventory. Instead of writing the same SQL code repeatedly in different applications or queries, you can encapsulate this logic into a stored procedure. When you need to perform that task, you simply call the stored procedure by its name, passing any necessary parameters.

Key advantages:

• Performance: Compiled once, executed many times, reducing parsing and optimization overhead.
• Security: Can grant execute permissions on procedures without granting direct table access, limiting exposure.
• Reusability: Write code once, use it across multiple applications and users.
• Maintainability: Changes can be made in one place (the procedure) and propagate everywhere.
• Reduced Network Traffic: A single call to a stored procedure can execute multiple SQL statements, sending less data over the network.

Creating Your First Stored Procedure

Let's create a simple stored procedure to select all products from a hypothetical 'Products' table.

CREATE PROCEDURE GetAllProducts
AS
BEGIN
    SELECT ProductID, ProductName, UnitPrice
    FROM Products;
END;
                    

Explanation:

• CREATE PROCEDURE GetAllProducts: This statement initiates the creation of a new stored procedure named GetAllProducts.
• AS: This keyword separates the procedure definition from its body.
• BEGIN...END: These statements define the block of code that constitutes the procedure's logic.
• SELECT ... FROM Products;: This is the T-SQL query that will be executed when the procedure is called.

To execute this procedure, you would use:

EXEC GetAllProducts;
-- or
EXECUTE GetAllProducts;
                    

Stored Procedures with Parameters

Procedures become more powerful when they accept input parameters. This allows for dynamic execution based on specific values.

Let's create a procedure to find products by their category.

CREATE PROCEDURE GetProductsByCategory
    @CategoryName NVARCHAR(50)
AS
BEGIN
    SELECT P.ProductID, P.ProductName, P.UnitPrice
    FROM Products AS P
    INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName = @CategoryName;
END;
                    

Explanation:

• @CategoryName NVARCHAR(50): This declares an input parameter named @CategoryName of type NVARCHAR with a maximum length of 50 characters.
• WHERE C.CategoryName = @CategoryName;: The query now filters results based on the value passed to the @CategoryName parameter.

To execute this parameterized procedure:

EXEC GetProductsByCategory @CategoryName = 'Beverages';
-- or
EXEC GetProductsByCategory 'Condiments';
                    

Output Parameters

Stored procedures can also return values using output parameters. This is useful for returning status information or calculated results.

CREATE PROCEDURE GetProductCountByCategory
    @CategoryName NVARCHAR(50),
    @ProductCount INT OUTPUT
AS
BEGIN
    SELECT @ProductCount = COUNT(P.ProductID)
    FROM Products AS P
    INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName = @CategoryName;
END;
                    

Explanation:

• @ProductCount INT OUTPUT: Declares an output parameter @ProductCount that will hold an integer value.
• SELECT @ProductCount = COUNT(...): The result of the COUNT function is assigned to the output parameter.

Executing with output parameters requires declaring a variable to receive the output:

DECLARE @Count INT;
EXEC GetProductCountByCategory @CategoryName = 'Dairy', @ProductCount = @Count OUTPUT;
SELECT @Count AS NumberOfDairyProducts;
                    

Modifying and Dropping Stored Procedures

You can modify an existing stored procedure using ALTER PROCEDURE. If you no longer need a procedure, use DROP PROCEDURE.

-- Modifying a procedure
ALTER PROCEDURE GetAllProducts
AS
BEGIN
    SELECT ProductID, ProductName, UnitPrice, UnitsInStock
    FROM Products;
END;

-- Dropping a procedure
DROP PROCEDURE GetProductsByCategory;
                    

Common Use Cases

• Performing complex data manipulations and business logic.
• Batch processing and scheduled tasks.
• Encapsulating multi-step operations.
• Creating custom APIs for data access.