LAB 3
This lab presents a quick overview of Stored
Procedure
DATABASE
SYSTEM 2
Lesson 3. Stored Procedure
• Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which
are grouped to perform a specific task.
• The main benefit of using a stored procedure is that it increases the performance of the database.
• The benefit of this is that network traffic is greatly reduced, because multiple T-SQL statements are
not forced to travel over the network individually.
• Only the name of the stored procedure to execute and the parameters to it need to be transmitted.
Benefits
• Simplify repeated tasks
• Run Faster
Stored procedures are cached on the server
• Reduce network traffic
• Help to provide security
Limit direct access to tables via defined roles in the database
• Errors can be handled in procedure code without being passed directly to client applications.
• Protecting against some SQL injection attacks.
• Stored procedures can be written once and accessed by many applications.
pg. 1
1- Create Procedure
Syntax:
Example:
create procedure insertProc @id int, @name nvarchar(100), @salary money as
Begin
insert into emp (id,Name,salary) values
(@id,@name,@salary)
end
2 EXECUTE Procedure
A stored procedure is used in the SQL Server with the help of the "Execute" or "Exec" Keyword.
Example:
Execute insertProc 9,2,'soha',5000
Example:
pg. 2
3- DROP Procedure
Syntax:
DROP PROCEDURE procedure_name;
4 ALTER Procedure
pg. 2
Example:
alter procedure insertProc @id int,@dept int, @name
nvarchar(100), @salary money
as begin
insert into emp (id,Name,dept_id,salary)
values (@id,@name,@dept,@salary)
end
5- Default Parameter
Example:
Create procedure sp_insert @id int, @fname
varchar(50), @lname varchar(50)='Ayyad' as
Begin
insert into Students (sid,fname,lname) values
(@id, @fname,@lname)
end
Executing the procedure
sp_insert 1458, 'Sarah' pg. 3
6- Optional Parameter – NULL Parameter
Example :
Create procedure insert_proc @id int, @fname
varchar(50), @lname varchar(50)
,@add varchar(50) = null as
Begin
insert into Students (sid,fname,lname
, address) values (@id, @fname,@lname ,
@add)
end
pg. 3
7 Output Parameter
Example: Procedure that give employee count by gender
create procedure spGetEmpCountByGender @gender char(10)
, @empCount int output as
begin
select @empCount =count(*) from Employee where gender =@gender
end
Executing the procedure
declare @x int
execute spGetEmpCountByGender 'male',@x output print @x
pg. 4
8 Return Values
create procedure
spGetEmpCountByGender2 @gender
char(10)
as
begin
return (select count(*) from Employee where
gender =@gender)
end pg. 4
Executing the procedure
declare @x int
execute @x= spGetEmpCountByGender2 'male' print @x
Difference between return values and output parameters
ReturnValue Output Parameter
Only Integer Datatype Any Datatype
Only one value returned More than one value returned
Use to convey success or failure Use to return values like id,
name,count()
pg. 5
Exercise 3
Design the following table and then answer the following questions:
1 Create a procedure that can insert full record in employee.
2 Create a procedure that will check for the salary of the employee id inserted, if the salary is more than or equal
2500 (then print ‘>=2500 ’), else (then print ‘<2500’).
3 Create a procedure that can update employee name by the new value giving
its id.
4Create a procedure that select employee data giving its department. 5- Using loop, Delete all records
starting from id = 1 to id =10.
6 Create a procedure that make use of the first procedure, so it can insert only id and name of employee.
7 Create procedure that insert into the Employee Table (ID, Name, Address, Salary, Department) with optional
parameter.
Example: You can execute the procedure insert_emp (id)
or insert_emp (id, Name)
or insert_emp (id, Name, Address)
or insert_emp (id, Name, Department)
or insert_emp (id, Name, Address, Department)
8Create procedure that returns number of employees per department. 9- Create procedure that takes emppg.id6
and returns its name and salary
