Creating Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that you can save and reuse. You can send them to the SQL Server for execution. Stored procedures reduce network traffic and improve application performance. They can also be used to enforce business logic and security.

What is a Stored Procedure?

A stored procedure is a compiled collection of Transact-SQL statements and optional control-of-flow logic that is stored on the server. Once stored, applications can execute it by simply calling its name. This is significantly more efficient than sending multiple Transact-SQL statements across the network for each execution.

Benefits of Using Stored Procedures:

Performance: Procedures are compiled once and stored in execution plan cache, reducing query compilation time.
Reduced Network Traffic: Instead of sending multiple SQL statements, you send only the procedure name and parameters.
Reusability: Write code once and call it from multiple applications or parts of an application.
Modularity: Break down complex tasks into smaller, manageable procedures.
Security: Grant execute permissions on procedures without granting direct table access.
Maintainability: Changes can be made to the procedure without affecting the calling application, as long as the interface remains the same.

Creating a Basic Stored Procedure

The basic syntax for creating a stored procedure uses the CREATE PROCEDURE statement. Here's a simple example:


CREATE PROCEDURE usp_GetAllCustomers
AS
BEGIN
    SET NOCOUNT ON; -- Prevents the "rows affected" message from being returned
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers;
END;
GO

Explanation:

CREATE PROCEDURE usp_GetAllCustomers: Defines a new stored procedure named usp_GetAllCustomers. The usp_ prefix is a common convention for user-defined stored procedures.
AS: Indicates the start of the procedure's Transact-SQL statements.
BEGIN...END: Encloses the body of the procedure.
SET NOCOUNT ON;: A common practice to suppress the "X rows affected" messages, which can sometimes interfere with application logic.
SELECT ... FROM Customers;: The actual Transact-SQL query to retrieve data.
GO: A batch separator used in SQL Server management tools.

Executing a Stored Procedure

Once created, you can execute a stored procedure using the EXECUTE or EXEC command:


EXEC usp_GetAllCustomers;
GO

Stored Procedures with Parameters

Stored procedures can accept input parameters to make them more dynamic. Parameters are defined after the procedure name.


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

Explanation:

@CustomerID INT: Declares an input parameter named @CustomerID of type INT.
WHERE CustomerID = @CustomerID;: Uses the parameter in the WHERE clause to filter the results.

Executing a Stored Procedure with Parameters

When executing a procedure with parameters, you provide the values for the parameters:


EXEC usp_GetCustomerByID @CustomerID = 5; -- Or simply: EXEC usp_GetCustomerByID 5;
GO

Tip: For procedures that return a single value (e.g., a count or an ID), you can use the OUTPUT parameter.

Modifying and Dropping Stored Procedures

To modify an existing stored procedure, use ALTER PROCEDURE. To remove a stored procedure, use DROP PROCEDURE.


-- To alter the procedure
ALTER PROCEDURE usp_GetAllCustomers
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, CompanyName
    FROM Customers; -- Modified to only return CustomerID and CompanyName
END;
GO

-- To drop the procedure
DROP PROCEDURE usp_GetAllCustomers;
GO

Understanding and utilizing stored procedures is fundamental to efficient and robust database development in SQL Server.

Microsoft Learn