Stored ProceduresStored procedure is the block of SQL statements written together to performs some specific task. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
We can pass parameters to procedures in three ways.
3) IN OUT-parameters
A procedure may or may not return any value.
Advantage of Stored Procedure
There are multiple advantages to using stored procedures:
- Stored procedures will generally perform better than the same group of statements executed individually. That is because when stored procedure code is compiled the execution algorithm is stored in the server procedure cache. Therefore, the SQL Server engine does not have to make a decision on how to run a stored procedure each time it is executed.
- Stored procedures can provide a high level of security to SQL Server data. If you write a stored procedure for every Data Modification Language statement (SELECT, INSERT, UPDATE, DELETE), then you can grant the database users access to these stored procedures, without giving permissions to the underlying tables. This way you can tightly control what users can do with your data.
- Stored procedures can accept and return parameters. Each stored procedure can have up to 2100 parameters.
- Stored procedures let you encapsulate business rules within manageable components of code. When the business rule changes, you only have to modify your code in one place.
- Stored procedures can be executed automatically when SQL Server starts up. Alternatively you can invoke stored procedures explicitly.
Example of Stored Procedure
Create Proc [dbo].[Dinner_SelectEmployee]
Select Firstname, Lastname, DateofBirth,Department from Employee
Use OUTPUT Parameter to Get data from Store Procedure
Create Stored Procedure
CREATE PROC usp_InserShipper
@ShipperID INT OUTPUT
INSERT INTO Shippers(CompanyName,Phone)
Get Output Paramenter by executing stored procedure
DECLARE @ShipperID INT
DECLARE @CompanyName VARCHAR(100)
DECLARE @Phone VARCHAR(12)
SET @CompanyName= 'My Company'
SET @Phone= '(503) 555-9831'
EXEC usp_InserShipper @CompanyName,@Phone,@ShipperID OUTPUT
SELECT @ShipperID --Get ShipperID using Output parameter