Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that you can group together as a logical unit to perform a specific task.

Stored procedures offer several benefits:

  • Improved Performance: Procedures are compiled and their execution plans are cached, leading to faster execution.
  • Reduced Network Traffic: Instead of sending multiple SQL statements, you send just the name of the procedure and its parameters.
  • Code Reusability: Procedures can be called from multiple applications.
  • Modularity: Break down complex tasks into smaller, manageable units.
  • Security: Grant execute permissions on procedures without granting permissions on the underlying tables.

Creating Stored Procedures

The basic syntax for creating a stored procedure is:


CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype = default_value
AS
BEGIN
    -- Transact-SQL statements
    SELECT column1, column2
    FROM YourTable
    WHERE column1 = @parameter1;
END;

Example: Creating a simple procedure


CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        c.CompanyName
    FROM
        Orders AS o
    JOIN
        Customers AS c ON o.CustomerID = c.CustomerID
    WHERE
        o.CustomerID = @CustomerID;
END;

To execute this procedure:


EXEC GetCustomerOrders @CustomerID = 10;
-- or
EXEC GetCustomerOrders 10;

Executing Stored Procedures

You can execute a stored procedure using the EXECUTE or EXEC keyword.


EXECUTE procedure_name [arguments];
-- or
EXEC procedure_name [arguments];

Arguments can be passed positionally or by parameter name.

Tip: Use parameter names when executing procedures to avoid confusion, especially when default values are involved or the order of parameters is complex.

Modifying Stored Procedures

To modify an existing stored procedure, use the ALTER PROCEDURE statement. The syntax is similar to CREATE PROCEDURE.


ALTER PROCEDURE procedure_name
    @new_parameter datatype = default_value
AS
BEGIN
    -- Modified Transact-SQL statements
    UPDATE YourTable
    SET column1 = @new_parameter
    WHERE SomeCondition;
END;

Dropping Stored Procedures

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


DROP PROCEDURE procedure_name;

System Stored Procedures

SQL Server provides a rich set of system stored procedures (often prefixed with sp_) that can be used to manage and query server and database information.

  • sp_help: Provides information about a database object.
  • sp_who: Lists currently active users and processes.
  • sp_configure: Displays or changes server configuration options.

Stored Procedure Parameters

Stored procedures can accept input parameters and return output parameters. The OUTPUT keyword is used to define an output parameter.


CREATE PROCEDURE CalculateTotalOrderAmount
    @OrderID INT,
    @TotalAmount DECIMAL(10, 2) OUTPUT
AS
BEGIN
    SELECT @TotalAmount = SUM(UnitPrice * Quantity)
    FROM OrderDetails
    WHERE OrderID = @OrderID;
END;

Executing a procedure with an output parameter:


DECLARE @MyTotal DECIMAL(10, 2);
EXEC CalculateTotalOrderAmount @OrderID = 10248, @TotalAmount = @MyTotal OUTPUT;
SELECT @MyTotal AS CalculatedTotal;