In this article describe the concept of stored procedure in sql server. Stored procedure is  a precompiled object. There are two types of stored procedure; system stored procedure and user defined store procedure. Here we try to demonstrate stored procedure by a good examples.

A stored procedure is a group of sql statements that has been created and stored in the database. A stored procedure is a precompiled object stored in the database. Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. Stored procedures are special objects available in SQL Server. It is a precompiled statement where all the preliminary parsing operations are performed and the statements are ready for execution.

Types of stored procedure

•        System stored procedure
•        User defined stored procedure

System stored procedure

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures. System stored procedure is those stored procedure which is available by the SQL server such as for renaming table name there is one stored procedure namely SP_RENAME , so such types of stored procedure are called system stored procedure.

User defined stored procedure

User Define stored procedure are those procedure which are developed by user for obtaining particular goal of task. The user are restrict to create stored procedure in master database with prefix name sp of stored procedure, because sp prefix name is used for system stored procedure in master database.

Creating stored procedure

Syntax

CREATE PROCEDURE <PROCEDURE_NAME> ( ---- PARAMETER OF STORED PROCEDURE)
AS
BEGIN
---------STORED PROCEDURE BODY
---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END
 

Example

CREATE PROCEDURE GetEmployeeName
AS
BEGIN
SELECT empFirstName+' '+empLastName FROM Employee
END

Syntax for call stored procedure

EXECUTE/EXEC <STORE_PROCEDURE_NAME>;

Example

EXEC GetEmployeeName;

Syntax for altering the procedure

ALTER PROCEDURE <PROCEDURE_NAME>
(
  ---- PARAMETER OF STORED PROCEDURE
  @PARAMETER_NAME1 DATATYPES,
  @PARAMETER_NAME2 DATATYPES,
  ........        .......
  .......         .......
)
AS
BEGIN
      -----STORED PROCEDURE BODY
      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND
END

Example

ALTER PROCEDURE GetEmployeeName
AS
BEGIN
SELECT empFirstName FROM Employee
END

Syntax for drop stored procedure

DROP PROCEDURE <PROCEDURE_NAME>

Example

DROP PROCEDURE GetEmployeeName;

User defined stored procedure can categorized into three types:

•     Non-Parameterized Stored Procedure
•     Parameterized Stored Procedure
•     Procedure with output parameter

Non Parameterized Stored Procedure

It is a normal stored procedure that cannot hold any parameter.

Syntax

CREATE PROCEDURE <PROCEDURE_NAME>
AS
BEGIN
---------STORED PROCEDURE BODY
---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END

Example

CREATE PROCEDURE NONPARAMETERIZED
AS
BEGIN
SELECT * FROM Employee
END

Parameterized Stored Procedure

The parameterized stored procedure that can accept parameter.

CREATE PROCEDURE <PROCEDURE_NAME> ( ---- PARAMETER OF STORED PROCEDURE)
AS
BEGIN
---------STORED PROCEDURE BODY
---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END

Example

CREATE PROCEDURE PARAMETERIZED (@employeeID varchar(50))
AS
BEGIN
SELECT * FROM Employee where empID=@employeeID
END
 
EXEC PARAMETERIZED 'EMP001'; /// to execute procedure with parameter

Procedure with output parameter

Stored Procedure with output parameter use to return some value from stored procedure. Output parameters are created in the same space as the input parameters, right between the procedure name and AS sections. The only difference is that they are defined with the word OUTPUT immediately afterward.

Syntax

CREATE PROCEDURE <PROCEDURE_NAME> ( ---- PARAMETER OF STORED PROCEDURE)
AS
BEGIN
---------STORED PROCEDURE BODY
---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END

Example

CREATE PROCEDURE OUTPUTPARAMETER
(
 @INPUT INT,
 @OUTPUT INT OUTPUT
)
AS
BEGIN
SET @OUTPUT = @INPUT+ 1
 
END

// to execute output parameter procedure store result

DECLARE @RESULT INT
EXEC OUTPUTPARAMETER '10', @RESULT OUT
SELECT @RESULT

 Advantages of stored procedure

·         Stored procedure allows modular programming. You can create the

procedure once, store it in the database, and call it any number of times in your

program.

·         Precompiled for faster performance

·         It provides better security because stored procedure only stored in the

database and any application only call the procedure.

·         Reduces surface area of attack for sql injection

·         More appropriate control over transactions and locking

·         Another advantage of stored procedure is allows for multiple clients in any

application procedure will be same.

Disadvantages of stored procedure 

·         Source control can be a pain.

·         Debugging is hard.

·         If you have a lot of functionality in procedures it will making swapping

between different database systems harder - It also creates more work if you want

to support different database systems.

·         Developing stored procedures can be a fairly specialized task, especially as

they get more complex.