Home > DeveloperSection > Articles > Stored procedure in SQL

Stored procedure in SQL


ASP.Net ASP.Net 
Ratings:
0 Comment(s)
 2683  View(s)
Rate this:

Stored procedure in SQL

 

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.



Don't want to miss updates? Please click the below button!

Follow MindStick