Store Procedure in SQL Server

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?

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? 

Simple click on New Query and Execute Following code.

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

@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';