What is a database stored procedure?

SQL databases can run your SELECT queries and manipulate data with DML statements like INSERT, UPDATE and DELETE. But, if you find your application executes the same SQL statements over and over, a stored procedure ("stor proc") provides a convenient way to store the same logic inside the database itself.

A stored procedure (SP) is a named collection of SQL statements that can be executed by applications or other database objects. You define one with the CREATE PROCEDURE statement. For example:

CREATE PROCEDURE GetCustomers
AS
SELECT *
FROM Customers

The procedure can then be executed with EXEC:

EXEC GetCustomers

Stored procedures can also accept parameters:

EXEC GetCustomerById 42

Keeping SQL logic inside the database simplifies application code and allows procedures to be reused by multiple applications.

Database systems such as Microsoft SQL Server also cache execution plans, which can improve performance for frequently executed procedures. This is handy when building multi-user database applications.