Home / Tutorials / SQL Server / Stored Procedures

Understanding SQL Server Stored Procedures

What are Stored Procedures?

Stored procedures are a set of one or more Transact-SQL statements that are compiled and stored in a SQL Server database. They can be executed by name and can accept input parameters and return output parameters or result sets. Stored procedures offer significant advantages in terms of performance, security, and reusability.

Key Benefits:

  • Performance: Procedures are compiled once and stored in memory, reducing parsing and execution overhead for subsequent calls.
  • Security: Grant execute permissions on a procedure without granting direct access to the underlying tables, preventing unauthorized data manipulation.
  • Reusability: Encapsulate complex logic into a single callable unit, promoting code consistency and reducing redundancy.
  • Maintainability: Changes to database logic can be made within the procedure without requiring changes to client applications, as long as the procedure signature remains the same.
  • Reduced Network Traffic: Instead of sending multiple SQL statements across the network, only a single CALL statement is sent.

Creating a Simple Stored Procedure

Let's create a basic stored procedure to retrieve all records from a hypothetical `Customers` table.

Example:

First, imagine we have a `Customers` table:

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100) ); INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES (1, 'John', 'Doe', 'john.doe@example.com'), (2, 'Jane', 'Smith', 'jane.smith@example.com');

Now, here's the stored procedure:

CREATE PROCEDURE GetAllCustomers AS BEGIN SELECT CustomerID, FirstName, LastName, Email FROM Customers; END;

Explanation:

  • CREATE PROCEDURE GetAllCustomers: This statement initiates the creation of a stored procedure named `GetAllCustomers`.
  • AS: This keyword separates the procedure definition from its body.
  • BEGIN ... END: These keywords define the block of Transact-SQL statements that the procedure will execute.
  • SELECT ... FROM Customers;: This is the core query that retrieves data from the `Customers` table.

Executing a Stored Procedure

To run the `GetAllCustomers` procedure, you can use the EXECUTE or EXEC command.

Execution Example:

EXECUTE GetAllCustomers; -- or simply EXEC GetAllCustomers;

This will return all rows and columns from the `Customers` table.

Stored Procedures with Parameters

Stored procedures can accept input parameters to make them more flexible and dynamic. Let's create a procedure to get customer information by their `CustomerID`.

Example:

CREATE PROCEDURE GetCustomerByID @CustomerID INT AS BEGIN SELECT CustomerID, FirstName, LastName, Email FROM Customers WHERE CustomerID = @CustomerID; END;

Explanation:

  • @CustomerID INT: This defines an input parameter named `@CustomerID` of type `INT`.
  • WHERE CustomerID = @CustomerID;: The query now filters results based on the provided parameter value.

Executing with a Parameter:

-- Execute for CustomerID 1 EXEC GetCustomerByID @CustomerID = 1; -- Another way to specify parameter EXEC GetCustomerByID 2;

Stored Procedures with Output Parameters

Output parameters allow a stored procedure to return a single value back to the calling environment.

Example:

CREATE PROCEDURE CountCustomers @CustomerCount INT OUTPUT AS BEGIN SELECT @CustomerCount = COUNT(*) FROM Customers; END;

Explanation:

  • @CustomerCount INT OUTPUT: Declares an output parameter named `@CustomerCount`.
  • SELECT @CustomerCount = COUNT(*) FROM Customers;: Assigns the count of customers to the output parameter.

Executing and Retrieving Output:

DECLARE @count INT; EXEC CountCustomers @CustomerCount = @count OUTPUT; SELECT @count AS TotalCustomers;