Loading lesson path
Concept visual
Start at both ends
A stored procedure is a precompiled SQL code that can be saved and reused. If you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. A stored procedure can also have parameters, so it can act based on the parameter value(s) that is passed.
Stored procedures are widely used in database management, and have the following benefits:
Formula
Code Reusability - The same procedure can be called from various applications
Improved Performance - Stored procedures are precompiled and runs faster
Database Security - You can set users permission to run a specific procedure (limits direct access to tables)
Easy Maintenance - When updating a procedure, it automatically updates all its useStored Procedure Syntax (SQL Server) To create a stored procedure, use the CREATE PROCEDURE statement:
procedure_name @param1 datatype, @param2 datatype AS
-- SQL_statements to be executed
column1, column2
table_name
columnN = @paramN
;
END;To see the syntax for MySQL database, look at MySQL Stored Procedures.
To run a stored procedure, use the EXEC statement:
procedure_name @param1 = 'value1', @param2 = 'value2'
;To delete a stored procedure, use the DROP PROCEDURE statement:
procedure_name
;does not return an error, if the procedure is missing, add the IF EXISTSclause:
procedure_name
;Below is a selection from the "Customers" table in the Northwind sample database: