Stored Procedures

Stored procedures are sets of Transact-SQL statements that are compiled and stored on the server. They can be executed repeatedly by multiple users and applications.

Overview of Stored Procedures

Stored procedures offer several advantages over ad-hoc SQL queries:

  • Performance: Stored procedures are pre-compiled and their execution plans are cached, leading to faster execution.
  • Reusability: A single stored procedure can be called from multiple applications or other stored procedures, reducing code duplication.
  • Modularity: They break down complex tasks into smaller, manageable units.
  • Security: Permissions can be granted on stored procedures, allowing users to perform specific actions without needing direct access to the underlying tables.
  • Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the procedure name and parameters are sent.

Creating Stored Procedures

The basic syntax for creating a stored procedure uses the CREATE PROCEDURE or CREATE PROC statement.


CREATE PROCEDURE  usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    -- SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement
    SET NOCOUNT ON;

    -- Select orders for the specified customer
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

Parameters

Stored procedures can accept input and output parameters. Input parameters are used to pass values into the procedure, while output parameters return values from the procedure to the calling application.


CREATE PROCEDURE  usp_CalculateOrderTotal
    @OrderID INT,
    @TotalAmount DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalAmount = TotalAmount
    FROM Orders
    WHERE OrderID = @OrderID;
END;

SET NOCOUNT ON

It's a common practice to include SET NOCOUNT ON at the beginning of a stored procedure. This prevents SQL Server from sending messages back to the client indicating the number of rows affected by each Transact-SQL statement. This can improve performance, especially for procedures that contain many statements.

Executing Stored Procedures

Stored procedures are executed using the EXECUTE or EXEC command.


-- Execute the procedure with an input parameter
EXEC usp_GetCustomerOrders @CustomerID = 10;

-- Execute a procedure with an output parameter
DECLARE @OrderTotal DECIMAL(10,2);
EXEC usp_CalculateOrderTotal @OrderID = 101, @TotalAmount = @OrderTotal OUTPUT;
SELECT @OrderTotal AS 'Calculated Order Total';

Modifying and Dropping Stored Procedures

Existing stored procedures can be modified using ALTER PROCEDURE and removed using DROP PROCEDURE.


-- Modify an existing procedure
ALTER PROCEDURE usp_GetCustomerOrders
    @CustomerID INT,
    @OrderDateThreshold DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID
      AND OrderDate > @OrderDateThreshold;
END;

-- Drop a procedure
DROP PROCEDURE usp_GetCustomerOrders;

Related Topics