Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that are created as a database object. They are used to perform specific tasks and can be executed by calling their name. Stored procedures offer several benefits, including improved performance, enhanced security, and code reusability.

Key Concepts

  • Definition: A stored procedure is created using the CREATE PROCEDURE statement.
  • Execution: Procedures are executed using the EXECUTE or EXEC statement.
  • Parameters: Procedures can accept input parameters and return output parameters.
  • Return Values: Procedures can return an integer status code to indicate success or failure.
  • Benefits:
    • Performance: The execution plan is compiled and cached, leading to faster execution on subsequent calls.
    • Security: Permissions can be granted on procedures rather than underlying tables, restricting direct access.
    • Reusability: Complex logic can be encapsulated and called from multiple applications or scripts.
    • Reduced Network Traffic: Multiple SQL statements are sent to the server as a single batch.

Creating a Stored Procedure

The basic syntax for creating a stored procedure 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 condition;
        -- More statements...
        RETURN 0; -- Optional: return status code
    END
    GO

Example: Creating a Simple Stored Procedure

This procedure retrieves customer names from a Customers table.

CREATE PROCEDURE usp_GetAllCustomers
    AS
    BEGIN
        SET NOCOUNT ON; -- Prevents sending DONE_IN_PROC messages to the client
        SELECT CustomerID, CompanyName, ContactName
        FROM Sales.Customers;
    END
    GO

Executing a Stored Procedure

To execute a stored procedure, use the EXECUTE or EXEC command.

EXECUTE usp_GetAllCustomers;
    -- or
EXEC usp_GetAllCustomers;

Stored Procedures with Parameters

Procedures can accept input parameters to make them more flexible.

CREATE PROCEDURE usp_GetCustomerByID
        @CustomerID INT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT CustomerID, CompanyName, ContactName
        FROM Sales.Customers
        WHERE CustomerID = @CustomerID;
    END
    GO

Executing this procedure:

EXEC usp_GetCustomerByID @CustomerID = 10;

Output Parameters

Output parameters allow a stored procedure to return values back to the calling program.

CREATE PROCEDURE usp_GetCustomerCountByCity
        @City NVARCHAR(50),
        @CustomerCount INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT @CustomerCount = COUNT(*)
        FROM Sales.Customers
        WHERE City = @City;
    END
    GO

Executing with an output parameter:

DECLARE @count INT;
EXEC usp_GetCustomerCountByCity @City = 'London', @CustomerCount = @count OUTPUT;
SELECT @count AS NumberOfCustomers;

Modifying and Dropping Stored Procedures

Use ALTER PROCEDURE to modify an existing procedure and DROP PROCEDURE to remove it.

-- Modify a procedure
ALTER PROCEDURE usp_GetAllCustomers
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Sales.Customers;
END
GO

-- Drop a procedure
DROP PROCEDURE usp_GetAllCustomers;
GO
Note: Always use SET NOCOUNT ON; at the beginning of your stored procedures to prevent unnecessary messages from being returned to the client, which can improve performance.
Tip: Consider using schema binding for views and functions if you want to ensure they are not modified in a way that would break the procedure.

Further Reading