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. 

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