Stored Procedure in SQL

Stored Procedures

Stored 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.
1) IN-parameters
2) OUT-parameters
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:

1. 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.
    
2. 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.
    
3. Stored procedures can accept and return parameters. Each stored procedure can have up to 2100 parameters.
    
4. 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.
    
5. 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]

as

Select Firstname, Lastname, DateofBirth,Department  from Employee

Use OUTPUT Parameter to Get data from Store Procedure

Create Stored Procedure

USE Northwind

CREATE PROC usp_InserShipper

@CompanyName      VARCHAR(100),

@Phone                  VARCHAR(12),

@ShipperID        INT         OUTPUT

AS

BEGIN

      INSERT INTO Shippers(CompanyName,Phone)

      VALUES(@CompanyName,@Phone)

     

      SET @ShipperID=SCOPE_IDENTITY();

END

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