Stored Procedures

A stored procedure is a saved block of SQL that you can run again and again with one call.

Create a stored procedure

sql

### CREATE PROCEDURE SelectAllCustomers
AS

### SELECT * FROM Customers;

### GO;

Run it

sql

### EXEC SelectAllCustomers;

With a parameter

sql

### CREATE PROCEDURE SelectCustomersByCity
    @City nvarchar(30)
AS

### SELECT * FROM Customers WHERE City = @City;

### GO;

### EXEC SelectCustomersByCity @City = 'London';

Why use stored procedures?

• Reuse: run the same logic from many places.
• Performance: the database can optimize the plan once.
• Security: hide the SQL from users.
• Consistency: everyone runs the exact same logic.

Drop a stored procedure

sql

### DROP PROCEDURE SelectAllCustomers;

Note

The exact syntax (@ parameters, GO) is SQL Server style. MySQL uses a different, slightly longer syntax.

TL;DR

• A stored procedure is reusable SQL saved in the database.
• Run it with EXEC.
• Parameters let you pass values in.
• Good for reuse, performance, and security.