Stored Procedures

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

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers;
GO;

Run it

EXEC SelectAllCustomers;

With a parameter

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

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.