Tuesday, October 13, 2015

SQL - Create Stored Procedure

How to create Stored Procedure
As we know that Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. To create a stored procedure, we need to use the CREATE PROCEDURE statement followed by the code that makes up the stored procedure. If you want to pass parameters into the stored procedure then they need to be included after the name. In our database, we have an Employee Master table such as given below:
EmpId
EmpName
Age
Salary
Department
101
Ryan Arjun
25
25000.00
IT
102
Will Smith
36
65000.00
FINANCE
103
Lucy Gray
43
25600.00
HR
104
Zed Millon
23
50000.00
ADMIN
105
Jony Bruce
34
60000.00
SALES
106
Little Man
28
26400.00
PROCUREMENT
107
Relahf Blood
56
68000.00
MARKETING

1) Now we need to create a stored procedure called proc_GetEmployeeDetails such as given below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---- Create sp
CREATE PROCEDURE proc_GetEmployeeDetails
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

---- Insert all the data into temp table
SELECT [EmpId]
      ,[EmpName]
      ,[EmpSalary]
      ,[EmpAge]
      ,[Department]
   into #TempEmployee
  FROM [dbo].[Employees] with (Nolock);

  ---- Select values from the temp table
  SELECT [EmpId]      ,[EmpName]      ,[EmpSalary      ,[EmpAge]       ,[Department]
from b #TempEmployee;

--- drop temp table
drop table #TempEmployee;
   
END
GO

After the stored procedure name we need to use the keyword "AS" and then the rest is just the regular SQL code that you would normally execute.

2) In SQL Server, you can create a stored procedure by right-clicking on the Stored Procedures node/folder and selecting Stored Procedure. This will open a template that's ready to be populated with our own specific procedure as shown below:

How to Execute Stored Procedure
1) After creating stored procedure in the database, we can call it any time whenever we want to execute it, we need to call it using either EXECUTE or EXEC.
---- Call stored procedure
Exec proc_GetEmployeeDetails
Go
EmpId
EmpName
Age
Salary
Department
101
Ryan Arjun
25
25000
IT
102
Will Smith
36
65000
FINANCE
103
Lucy Gray
43
25600
HR
104
Zed Millon
23
50000
ADMIN
105
Jony Bruce
34
60000
SALES
106
Little Man
28
26400
PROCUREMENT
107
Relahf Blood
56
68000
MARKETING

---- Call stored procedure
Execute proc_GetEmployeeDetails
Go
EmpId
EmpName
Age
Salary
Department
101
Ryan Arjun
25
25000
IT
102
Will Smith
36
65000
FINANCE
103
Lucy Gray
43
25600
HR
104
Zed Millon
23
50000
ADMIN
105
Jony Bruce
34
60000
SALES
106
Little Man
28
26400
PROCUREMENT
107
Relahf Blood
56
68000
MARKETING

2) We can execute the stored procedure from the Microsoft SQL Server Management Studio as given below:
Learn more on another feature of SQL as:
  1. SQL - Stored Procedure
  2. SQL - Create Stored Procedure
  3. SQL - Execute Stored Procedure
  4. SQL - Alter Stored Procedure
  5. SQL - Views
  6. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
  7. SQL - Merge Statement
  8. SQL - Functions
  9. SQL - Cursors

1 comment:

  1. Stored Procedure is most popular database object. A stored procedure in SQL Server is a group of one or more Transact-SQL statements.
    https://code-lake.blogspot.com/2016/11/stored-procedure-sql-server.html

    ReplyDelete