Tuesday, October 13, 2015

SQL - Alter Stored Procedure

Modified or Alter Stored Procedure
We have an existing stored procedure called proc_GetEmployeeDetails which was created in our last article “SQL – Create Stored Procedure”.  Now, we want to add a parameter to get the filtered records based on the parameter value in that stored procedure. 
For this, simply replace the CREATE with ALTER syntax, add parameter in the round closed (@inpDept Varchar (20)) after procedure name and in the select statement need to add where clause as given below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---- Alter existing stored procedure
---- By adding parameter
ALTER PROCEDURE proc_GetEmployeeDetails
(@inpDept Varchar (20))
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]
---- Temp table
into #TempEmployee
FROM [dbo].[Employees] with (Nolock)
---- add parameter filter
Where [Department]= @inpDept ;

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

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

After the stored procedure name we need to use parameter with data type, the keyword "AS" and then the rest is just the regular SQL code that you would normally execute.
How to Execute Stored Procedure
After modifying existing 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 @inpDept='IT'

Go
EmpId
EmpName
Age
Salary
Department
101
Ryan Arjun
25
25000
IT

---- Call stored procedure
Execute proc_GetEmployeeDetails @inpDept='IT'
Go
EmpId
EmpName
Age
Salary
Department
101
Ryan Arjun
25
25000
IT

We can execute the stored procedure from the Microsoft SQL Server Management Studio as given below:

After click on the Execute Store Procedure, It’ll launch the following window, where we have set the desired parameter value.

Now, we need to click OK button to execute the stored procedure and It will show the following result as shown below:

As now, we can easily modify the existing stored procedure as per our requirement. 

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands