Sql Stored Procedures
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So 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.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
Syntax for Stored Procedure:
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Syntax for Stored Procedure execution:
EXEC procedure_name;
Example:
Creating stored procedure to retrieve data from Employees data.
Syntax for creating procedure
Create Procedure AllRecords1
AS
Select * from HR_EMPLOYEES
GO;
Syntax for execution of stored procedure
Exec AllRecords1;
Output of the above syntax given below
Stored Procedure with one parameter
The following SQL statement creates a stored procedure that selects employees from a particular location from the “Employee” table
Syntax:
Create Procedure AllRecords2 @EmpLoc varchar(250)
AS
select * from HR_EMPLOYEES where EMP_LOCATION=@Emploc;
Syntax for execution of stored procedure
Exec AllRecords2 @EmpLoc=’Pune’;
Retriving data of the employees whose location is Pune.
Output of the above syntax given below
Stored Procedure with multiple parameters
Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.
The following SQL statement creates a stored procedure that selects employees from a particular City with a particular salary from the “employees” table:
Syntax:
Create Procedure AllRecords3 @EmpLoc varchar(250) , @EmpSal int
AS
select * from HR_EMPLOYEES where EMP_LOCATION=@Emploc or EMP_SAL=@EmpSal;
Syntax for execution of stored procedure
Exec AllRecords3 @EmpLoc=’Pune’ , @EmpSal=’34000′;
Output of the above syntax given below
