SQL Server Stored Procedures

Stored procedures in SQL Server are pre-compiled SQL code blocks that can be saved and reused multiple times. They offer several advantages, including improved performance, code reusability, and enhanced security.

What is a Stored Procedure?

Think of a stored procedure as a mini-program within your database. It encapsulates a set of SQL statements that perform a specific task. Instead of writing the same SQL query repeatedly, you write it once as a stored procedure and then simply call (execute) the procedure whenever you need to perform that task. This simplifies your application code and improves efficiency.

Benefits of Stored Procedures

• Reusability: Write the code once, use it many times.
• Performance: Pre-compilation can lead to faster execution.
• Security: Stored procedures can help control database access.
• Modularity: Break down complex tasks into smaller, manageable units.

Stored Procedure Syntax

Creating a Stored Procedure

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
      

Executing a Stored Procedure

EXEC procedure_name;
      

Example Database

Here's a sample of the 'Customers' table used in the examples:

Examples

Simple Stored Procedure (No Parameters)

This stored procedure selects all customers.

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

EXEC SelectAllCustomers;
      

(The entire Customers table will be displayed here)
      

Stored Procedure with One Parameter

This procedure selects customers from a specific city.

CREATE PROCEDURE SelectCustomersByCity
@City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectCustomersByCity @City = 'London';
      

(Only the row for 'Around the Horn' will be displayed)
      

Stored Procedure with Multiple Parameters

This procedure filters customers by city and postal code.

CREATE PROCEDURE SelectCustomersByCityAndPostalCode
@City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectCustomersByCityAndPostalCode @City = 'London', @PostalCode = 'WA1 1DP';
      

(Only the row for 'Around the Horn' will be displayed)