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