MSDN Documentation: SQL Stored Procedures

What are Stored Procedures?

Stored procedures are precompiled sets of one or more Transact-SQL statements stored on the SQL Server. They offer significant advantages in terms of performance, security, and maintainability compared to ad-hoc queries.

Key benefits include:

• Performance: Procedures are compiled and execution plans are cached, leading to faster execution on subsequent calls.
• Reduced Network Traffic: Instead of sending multiple SQL statements, only the procedure name and parameters are sent over the network.
• Reusability: A single procedure can be called from multiple applications, promoting code reuse.
• Modularity: Encapsulates complex logic, making applications easier to develop and maintain.
• Security: Permissions can be granted on stored procedures without granting direct access to underlying tables.

Creating Stored Procedures

You can create stored procedures using the CREATE PROCEDURE or CREATE PROC statement. The basic syntax is:

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype = default_value
AS
BEGIN
    -- SQL statements to be executed
    SELECT column1, column2 FROM your_table WHERE condition = @parameter1;
END;

Let's create a simple procedure to retrieve customer details by ID:

CREATE PROCEDURE dbo.GetCustomerByID
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents the 'rows affected' message
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Sales.Customers
    WHERE CustomerID = @CustomerID;
END;

Executing Stored Procedures

Stored procedures are executed using the EXECUTE or EXEC statement.

EXECUTE procedure_name @parameter1 = value1, @parameter2 = value2;

Using the GetCustomerByID procedure created earlier:

EXECUTE dbo.GetCustomerByID @CustomerID = 10;

You can also execute procedures using the EXECUTE statement without specifying the owner if it's in the default schema.

Parameters

Stored procedures can accept input parameters, allowing for dynamic query execution. Parameters are declared with an @ prefix, followed by the name and data type.

You can define default values for parameters, making them optional.

Example with an optional parameter:

CREATE PROCEDURE dbo.GetOrdersByCustomerID
    @CustomerID INT,
    @OrderDate DATETIME = NULL -- Optional parameter
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, ShipCity
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
      AND (@OrderDate IS NULL OR OrderDate = @OrderDate);
END;

Executing with and without the optional parameter:

-- Get all orders for CustomerID 10
EXECUTE dbo.GetOrdersByCustomerID @CustomerID = 10;

-- Get orders for CustomerID 10 on a specific date
EXECUTE dbo.GetOrdersByCustomerID @CustomerID = 10, @OrderDate = '2023-10-27';

Return Values

Stored procedures can return an integer status code using the RETURN statement. This is typically used to indicate success or failure. A return value of 0 generally signifies success.

CREATE PROCEDURE dbo.AddProduct
    @ProductName NVARCHAR(100),
    @UnitPrice MONEY
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT ProductName FROM Production.Products WHERE ProductName = @ProductName)
    BEGIN
        RETURN 1; -- Indicate product already exists
    END
    ELSE
    BEGIN
        INSERT INTO Production.Products (ProductName, UnitPrice)
        VALUES (@ProductName, @UnitPrice);
        RETURN 0; -- Indicate success
    END
END;

To retrieve the return value, use the @@ERROR system function or declare a variable and use the EXECUTE statement with an output parameter.

DECLARE @ReturnStatus INT;
EXECUTE @ReturnStatus = dbo.AddProduct @ProductName = 'New Gadget', @UnitPrice = 19.99;

IF @ReturnStatus = 0
    PRINT 'Product added successfully.';
ELSE
    PRINT 'Product already exists.';

Modifying Stored Procedures

You can modify an existing stored procedure using the ALTER PROCEDURE statement. This statement has the same syntax as CREATE PROCEDURE.

ALTER PROCEDURE dbo.GetCustomerByID
    @CustomerID INT,
    @IncludeOrderCount BIT = 0 -- Added an optional parameter
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Sales.Customers
    WHERE CustomerID = @CustomerID;

    IF @IncludeOrderCount = 1
    BEGIN
        SELECT COUNT(OrderID) AS NumberOfOrders
        FROM Sales.Orders
        WHERE CustomerID = @CustomerID;
    END
END;

Deleting Stored Procedures

To remove a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE dbo.GetCustomerByID;

You can use IF EXISTS to avoid errors if the procedure doesn't exist:

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = 'GetCustomerByID')
    DROP PROCEDURE dbo.GetCustomerByID;

Performance Considerations

• Avoid Cursors: Cursors process rows one by one and are generally slow. Set-based operations are preferred.
• Use SET NOCOUNT ON: Prevents SQL Server from sending "X rows affected" messages, reducing network overhead.
• Parameter Sniffing: SQL Server caches execution plans based on the parameters used in the first execution. If subsequent executions use vastly different parameter values, performance can degrade. Consider using OPTION (RECOMPILE) or optimize for specific parameter values if this is an issue.
• Dynamic SQL: Use dynamic SQL sparingly, as it can be harder to optimize and may introduce security risks if not handled carefully.
• Indexing: Ensure appropriate indexes exist on tables referenced within your stored procedures.

Security Aspects

• SQL Injection: Always use parameterized queries or stored procedures to prevent SQL injection vulnerabilities. Never concatenate user input directly into SQL statements.
• Permissions: Grant EXECUTE permission on stored procedures instead of direct table access. This limits the scope of user actions.
• Principle of Least Privilege: Ensure the SQL Server login executing the stored procedure has only the necessary permissions.
• Schema Binding: For certain scenarios, you can bind stored procedures to schemas to improve security and maintainability.