What is StoreProcedure in SQL.

Storeprocedure:-

1. It contains group of transaction statement. It reduce creating dml, ddl operation operation if it is in repeated state. execution done by it's name.

Storeprocedure are two type IN and OUT parameters.

sp_helptext spname  it will show the entire logic of storeprocedure you have written for that.
alter spname :-  modify purpose syntax we use.
drop spname:-  remove the storeprocedure.

for an ex:-
if we are using select statement such as

select  empno, ename, deptno from employee.
we are calling  again an again and this is lengthy, So better to wrap in storeprocedure.

program

create procedure getemployeedetail  //  procedure /proc can be call in short
as
begin
select [Empno],[Ename],[Deptno] from [dbo].[Employees]
end


exec  getemployeedetail // called by name only

Output:-

2212 Amit 30
7369 SMITH 20


Parameteric Storeprocedure

ex:-

Create  procedure getemployeedetail(@empno int) // parametric SP
as 
begin   
select [Empno],[Ename],[Deptno] from [dbo].[Employees]  where [Empno]=@empno
end

execution with sp name with exact parameter value  thats why order is impotant.
we can call two ways:-
1. exec  getemployeedetail 7369
2. exec getemployeedetail  Empno = 7369 // here parameter order is not recommended.


Storeprocedure with output parameter.

1. To use Output parameter storeprocedure we use keyword as OUT or OUTPUT.

Example
Here  we have 2 parameter @empno and @count differences is that @empno is input parameter and @count outputs parameter

Create procedure getemployeedetailwithoutputparam 
@empno int ,
@count int output
as 
begin
   select @count = count(*) from  [dbo].[Employees]  where [Empno]=@empno
end

execution in 2 ways

Declare @Totalemp int
exec  getemployeedetailwithoutputparam 7369,@Totalemp out
print @Totalemp
------
Declare @Totalemp int
exec  getemployeedetailwithoutputparam 7369,@count=@Totalemp out
print @Totalemp

Output :- 1

Author : praneet kumar

Share this