Store Procedure is
subroutine available to Application that access Relational
Database System.
Few important things are
to be noted:
- Not like Views in Access SP (Store Procedure) are pre-compiled in database.
- Keyword is PROC or PROCEDURE.
- Increase the Usability in Query Optimization.
- Store in Database.
- Wipe out from memory after their execution.
How to code?
Open SQL-Server, Create new Database named as EmployeeDB and
Table named as Employee
Add following fields names in table
Make sure ID set as auto increment and Primary Key
Expand Tables =>
right-click on dbo.Employee =>
Edit Top 200 Rows and Add dummy data like
following
To make sure data is added, click on New Query
(placed at right
corner), and paste following code and
Click
SELECT * FROM [EmployeeDB].[dbo].[Employee];
How to Create Store Procedure ?
·
Using same query we create store
procedure .
·
Paste/Replace following code and click Execute.
CREATE PROC SPAllEmployee
AS
SELECT * FROM [EmployeeDB].[dbo].[Employee];
It create store procedure named as
SPAllEmployee.
You can also see that Store
Procedure file is created in Database => EmployeeDB => Programmability =>
Store Srocedures.
Now,
How To Call Store?
How To Call Store?
Click New Query Paste following Code and Execute.
Exec SPAllEmployee
How to Alter Store Procedure ?
·
Using same Store Procedure SPAllEmployee
we alter this SP(Store Procedure). Now we are going
to change the query . Click New Query Paste Following Code and Click Execute.
ALTER PROC SPAllEmployee
AS
SELECT * FROM [EmployeeDB].[dbo].[Employee] AS E
WHERE E.City='Lahore';
Click New Query Paste Following Code and Execute
Exec SPAllEmployee
See query result
is changed now.
How to Drop Store Procedure?
DROP PROCEDURE SPALLEmployee;
How to Create Store Procedure with Parameter?
Again create new Store Procedure
named as SPnewEmployee in New Query and Execute following lines of code.
CREATE PROC SPnewEmployee
CREATE PROC SPnewEmployee
@param varchar(50)
AS
SELECT * FROM [EmployeeDB].[DBO].[Employee] AS E
WHERE E.[City]=@param;
EXEC SPnewEmployee 'Lahore'
If you want pass two or more
parameter just separated with comma.
Now create new Store Procedure in New Query and Execute following lines of code it.
CREATE PROC SPnewEmployeeTwo
@city varchar(50),
@departmentName varchar(50)
AS
SELECT * FROM [EmployeeDB].[DBO].[Employee] AS E
WHERE E.[City]= @city AND E.DepartmentName=@departmentName;
And now click New Query paste following code and then click on Execute.
EXEC SPnewEmployeeTwo @city='Lahore', @departmentName='English';
