What is a stored procedure?
A stored procedure is a collection of SQL statements that are pre-compiled and stored in databases. Stored Procedures are defined with a Name, parameter list, and SQL statements and these SQL statements can be single or multiple.
When a procedure calls itself it is called a recursive stored procedure, all DBs support them except MYSQL.
When do we need them?
Stored procedures are needed when we need to execute the same statement/queries multiple times, we create a stored procedure for them and store them in the database whenever those statements are required to run just call the stored procedure.
Here are a few advantages of Stored procedures
- Reusable set of statements
- Protection over SQL injections
- Stored procedures can be shared in between different applications
- One place modification required
- Reduce traffic between DB and the application
Create Stored Procedures –
SYNTAX –
CREATE PROCEDURE procedure_name
AS
SQL statement/query
GO;
Problem: Create a stored procedure named “SelectAllEmployees” that selects all records from the “Employees” table having salary greater than 50K:
Stored Procedure –
CREATE PROCEDURE SelectAllEmployees
AS
SELECT * FROM Employees where salary > 50000;
GO;
Execute it –
EXEC SelectAllEmployees;
Study Section provides a big list of certification exams through its online platform. The French Certification exam can help you to certify your skills to communicate in the French language. Whether you are new to the language or you are an expert in it, this French certification exam can test the ability of anybody’s command over the French language.
