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
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 to execute
Example for creating non paramentrize stored procedure
create procedure stuDentDetails as
select * from Student
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
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
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
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
print 'List of the student having same age'
select * from Student where sage=@sage
Executing parametrized procedure
exec prcStudentDetails 22
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
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
if exists(select * from Student where sid=@sid)
select @sname=sname,@scity=scity from Student wheresid=@sid
2) create procedure showDetails @sid varchar(5)
declare @returnValue int
declare @sname varchar(30)
declare @scity varchar(30)
exec @returnValue = prcStudentOutputDemo @sid,@sname output,@scity output
if(@returnValue = 0)
print 'Student Name '+@sname
print 'Student City '+@scity
print 'No records found'
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'
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
Example for altering the procedure
alter procedure stuDentDetails as
select * from Student
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