Stored Procedures

a precompiled program that is stored in a database

It is typically used to perform a specific action or task, often multiple times within a database. This can include querying data, modifying data, or performing a combination of both.

Stored procedures are commonly used to improve database performance by reducing the amount of time and resources required to execute frequently used queries.

In addition, they can help to enforce business rules and improve the security of a database by limiting direct access to the underlying data.

Stored Procedure vs Functions

• A stored procedure is executed directly by the database engine, while a function is executed within the context of a programming language.

  • This means that a stored procedure can be called directly from within a database query, while a function must be called from within a program.

• A stored procedure can modify data in the database, while a function cannot.

  • This is because a stored procedure is executed directly by the database engine, which has the ability to make changes to the data in the database. A function, on the other hand, is executed within the context of a program, and therefore does not have direct access to the database.

Example

CREATE PROCEDURE procedure_name @param1 datatype, @param2 datatype
AS
UPDATE table_name
SET column_name1 = @param1
WHERE column_name2 = @param2;

EXEC procedure_name param1, param2;