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.


  Modified On Nov-24-2017 10:17:47 AM

Leave Comment