articles

Home / DeveloperSection / Articles / Implementing Stored Procedure

Implementing Stored Procedure

Anonymous User11137 27-Jan-2011

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. 

Stored Need of 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

       create is a keyword to create a database command

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

       as is a keyword

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

                     sqlstatement1/sqlsatement2 are the sqlstatements that you want toexecute

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  exec is 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 prcStudentDetails22

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 wheresid=@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


alterprocedure 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.

dropprocedure procedureName

Example for droping procedure

dropprocedure stuDentDetails

 


Updated 28-Mar-2020
I am a content writter !

Leave Comment

Comments

Liked By