- Set of logical group of SQL statements which are grouped to perform a specific task.
Benefits of Using the Stored Procedure
- Reduces the amount of information sent to the database server.
- Compilation step is required only once when the stored procedure is created.
- It helps in re usability of the SQL code
- Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure
- it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.
Difference between Stored Procedure and Function:
- A Stored Procedure may or may not return a value but a function always returns a value.
- We can use a function in a Stored Procedure but we can’t use a Stored Procedure in a Function
Type of Stored Procedure
- System Stored Procedure
- User Defined Stored Procedure
Types of User Defined Store Procedure:
- Non-parameterized Stored Procedure
- Parameterized Stored Procedure
Syntax for Stored Procedure
Create Procedure Procedure-name
Input parameters ,
Output Parameters (If required)
Sql statement used in the stored procedure
Stored Procedure using out Parameter
OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program.
create proc sp_OutEx @id int , @name varchar(50) output
select @name=Name+’,’+FatherName from Employee where id=@id
declare @fetchresult varchar(50)
exec sp_OutEx 3,@fetchresult out