Home > DeveloperSection > Articles > Implementing Stored Procedure

Implementing Stored Procedure


Database Database 
Ratings:
0 Comment(s)
 6702  View(s)
Rate this:

Implementing Stored Procedure

Stored procedures are defined as precompiled object stored in a database. Here I had mentioned precompiled object that means such type of entity which is compiled and after compilation the form that we get is stored in database.

Need of Stored Procedures

When we are talking about to develop any enterprise application then efficiency is major points that we have to mention. We want to make such type of projects which is efficient in terms of input output. When we execute any SQL script or batch more than once, you need to recreate SQL statements and submit them to the server. This process leads to increase the load upon the server in terms of compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. Stored procedures can invoke all types’ database commands such as DDL (Data Definition Language), DML (Data Manipulation Language) or DQL (Data Query Language) and can return values.

Types of procedure

1.       Non Parameterize Procedure

2.       Parameterize Procedure

Non - parameterize procedure means such types of procedure which does not need values at run time which simply returns output of the query while parameterized procedure are such types of procedure which requires value at run time.

Syntax for creating Non-Parameterize procedure

create procedure/proc procedure_name as

begin

     sqlstatement1

     sqlstatement2

end

where

       createis a keyword to create a database command

       procedure/procis a keyword which tells that the object that is created is a procedure

       asis a keyword

       begin/endis a keyword which is used to create a block

                     sqlstatement1/sqlsatement2 are the sqlstatements that you want to execute

Example for creating non paramentrize stored procedure

create procedure stuDentDetails as

begin

     select * from Student

end

After executing these steps procedure is created and stored in a database. Now for executing the procedure type following syntax in your SQL browser.

Syntax for executing Procedure

exec procedure_name
where   execis a keyword which is used to execute your procedure

                  procedure_nameis the name of the procedure that you want to execute

Example for executing non parameterize stored procedure

Exec stuDentDetails

Output-

Implementing Stored Procedure

The steps that server performed to create a stored procedure

Ø  In the first phase the procedure is compiled that is syntax for creating procedure is checked and then its component are broken into various pieces. This process is known as parsing.

Ø  The existence of the referred objects such as tables and views are checked. This process of checking tables and views is known as resolving.

Ø  The name of the procedure is stored in the sysobjects tables and the code that creates the stored procedure is stored in the syscommnets table.

Ø  The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as an execution plan.

Ø  When the procedure is executed for the first time, the execution plan will be read and fully optimize and then run.

Creating parameterize stored procedure

When we want to pass values to the procedure at run time then we have to create parameterize procedure.

Syntax for creating parameterized procedure

create procedure/proc procedureName parameterName datatype as

begin

     sqldatement1

     sqlstatement2

end

where

                procedureName -- is the name of the procedure that you want to create     parameteName --is the name of the parameter that you want to create. Make sure parameter name should be start with@ symbol                                                                               datatype --Data type of your parameter such as varchar, char or int

Example for creating parameterized procedure

create procedure prcStudentDetails @sage int

as

begin

     print 'List of the student having same age'

     select * from Student where sage=@sage

end

Executing parametrized procedure

exec prcStudentDetails 22

output

Implementing Stored Procedure

Creating parameterize procedure by using output keywords

Sometimes we need such types of procedure which returns some value in the variable. Simple procedure does not return any value. They execute only the query. Whenever we want procedures which return a value and we can use those values in different procedure than we need to create a procedure by using output keyword. To specify a parameter as the output parameter, you can use the output keyword.

Syntax for declaring an output parameter using the output keyword

create procedure procedurename @parameterName datatype output

as

begin

     sqlstatement1

     sqlstatement2

end

Example to demonstrate the use procdure which use output keyword

1)      create procedure prcStudentOutputDemo @sid varchar(5),@sname varchar(20) output,@scity varchar(20)output

as

begin

     if exists(select * from Student where sid=@sid)

     begin

          select @sname=sname,@scity=scity from Student wheresid=@sid

          return 0

     end

     else

          return 1

end

 

2)      create procedure showDetails @sid varchar(5)

as

begin

     declare @returnValue int

     declare @sname varchar(30)

     declare @scity varchar(30)

     exec @returnValue = prcStudentOutputDemo @sid,@sname output,@scity output

     if(@returnValue = 0)

     begin

          print 'Student Name '+@sname

          print 'Student City '+@scity

     end

     else

          print 'No records found'

end

In the first example I had created a procedure by using output key word and in second example I had created a procedure which use that procedure. This steps also known as calling one procedure inside another procedure.

Executing the procedure

exec showDetails 'S0001'

 

output

Implementing Stored Procedure

Some useful command related with procedure

Ø  sp_help <procedureName> gives the description of the procedure that you have created.

Ø  Sysobject By using sysobjects view you can search all the procedure that you have created. Sysobject view provides a table where all the procedure are stored.

Altering a stored procedure

Whenever you want to modify the stored procedure then use the alter procedure statement. The syntax of alter procedure statement is
alter procedure procedureName as

begin

     sqlstatement1

     sqlstatement2

end

Example for altering the procedure

alter procedure stuDentDetails as

begin

     select * from Student

end

Droping procedure from database

You can drop a procedure from a database by using drop command. The syntax for droping procedure is as follows.

drop procedure procedureName

Example for droping procedure

drop procedure stuDentDetails

 


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

Follow MindStick