Stored Procedure : Stored Procedure Mohammad Waheed
We will cover : We will cover SP Overview
Syntax
Examples
Summary
Lab
Let’s make it simple : Let’s make it simple What is a procedure? (forget about Database for a second)
Its a particular course of action intended to achieve a result
“the procedure of obtaining a driver's license”
Procedure / routine: a set sequence of steps
Get it?
Give me a real world example of procedure.
Three SQL Statement vs. One SP : Three SQL Statement vs. One SP Why write more than we need to.
Stored Procedure / SP Overview : Stored Procedure / SP Overview Contains declarative or procedural SQL statement
You are familiar with most of SP given that you are capable writing sql statements
Pass parameters
Advantages… : Advantages… Compiled
Better response time
Reduces connection between app vs db server
Stored procedures can be used for database security purpose because each store procedure can have its own database privileges
Reusability
Easy to maintain
Syntax 1 : Syntax 1 CREATE PROCEDURE sp_GetStudents
AS
SELECT Student_Name
FROM Student_Table
___________
You call this proc by typing
Exec sp_GetStudents()
Syntax 2 : Syntax 2 CREATE PROCEDURE spName
(
@sid int
)
AS
SELECT col1, col2, col2
FROM tableName
WHERE colid = @sid
Define deleting stu SP : Define deleting stu SP CREATE PROCEDURE mysp_deleteStu
( @sid int )
AS
DELETE FROM students
WHERE studentid = @sid
Syntax…execute : Syntax…execute Execute spName
Pass param
Execute spName param1
Delete student ID 7
Execute mysp_DeleteStu 7
SP using views : SP using views CREATE PROCEDURE mysp_stuList
( @location varchar(20) )
AS
SELECT * FROM viewStuList
WHERE city = @location
You can define the view in another place
Begin….. End : Begin….. End CREATE PROCEDURE Delete_Employee
(@EmployeeId INT)
AS
BEGIN
DELETE FROM Employees
WHERE EmployeeId = @EmployeeId;
END
Now your turn… #1 : Now your turn… #1 Create SP_Faculty and return all faculty name & salary from table:
Faculty –
- facultyid
- name
- salary
- city
Solution to #1 : Solution to #1 CREATE PROCEDURE SP_Faculty
AS
SELECT name, salary
FROM faculty
----
Exec SP_Faculty
Now your turn… #2 : Now your turn… #2 Create SP_Faculty and return all faculty name & salary where salary greater than $50,000 from table:
Faculty –
- facultyid
- name
- salary
- city
Solution to #2 : Solution to #2 CREATE PROCEDURE SP_Faculty
( @salary int )
AS
SELECT name, salary
FROM faculty
WHERE salary > @salary
----
Execute SP_Faculty 50000
Now your turn… #3 : Now your turn… #3 Delete all faculty from Albany
Solution to #3 : Solution to #3 CREATE PROCEDURE mysp_delFac
( @location varchar(20))
AS
DELETE FROM faculty
WHERE city = @location
----
Execute mysp_felFac ‘Albany’