In this article, we will learn to modify the created stored procedure in MS SQL.You can modify the Stored Procedure in two ways. one is by using a client called SSMS and other way is by using T-SQL statements + SSMS in MS SQL Server.
Method 1: Using SQL Server Management Studio (SSMS) to Modify the Stored Procedure
- In Object Explorer, connect to an Database Engine.
- Expand the database in which the procedure belongs.
- Expand Stored Procedures, right-click the procedure, and then select Modify.
- Modify the stored procedure through the readily available SQL statement.
Method 2: Using T-SQL statements and SSMS.
- In Object Explorer of SSMS, connect to an Database Engine.
- Create a new data base
- Use the Database created
- Open a new Query page using SSMS
- Modify the Procedure using ALTER PROCEDURE statement
Example 1:
In this example, we will use T-SQL to create and modify a Stored Procedure by the name My_procedure in a Database. First Create two pages in SSMS, one for creating Procedure and other for Modifying.
Creating a Procedure:
CREATE DATABASE Sample_DB; GO USE Sample_DB; GO CREATE PROCEDURE My_Procedure @first_name varchar(20), @last_name varchar(20) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @first_name , @last_name; END GO EXEC My_Procedure 'Andy', 'Jessy';
Output : The output shows creation and execution of the above mentioned procedure
Modifying the Created Procedure:
ALTER PROCEDURE [dbo].[My_Procedure] @first_name varchar(20), @last_name varchar(20) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @last_name + ', ' + @first_name; END
Output : Execute the above code and then run the procedure in a separate page
Example 2:
In this example , we are modifying the same Procedure created in the above example in a different way. Create 3 pages in SSMS , one for creating Procedure , 2nd for Modifying and 3rd for executing modified procedure .
The aim is adding 3 parameters and one case statement to the My_procedure.
Page 1 : Creating Procedure
CREATE PROCEDURE My_Procedure @first_name varchar(20), @last_name varchar(20), @choice int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @first_name , @last_name , @choice; END GO EXEC My_Procedure 'Andy', 'Jessy' , 1;
Page 2 : Modifying Procedure
ALTER PROCEDURE [dbo].[My_Procedure] @first_name varchar(20), @last_name varchar(20), @choice int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT 'Name' = case @choice when 1 then @first_name when 2 then @last_name ELSE @first_name + ' ' + @last_name END END
Page 3 : Executing the modified procedure
EXEC My_Procedure 'Andy', 'Jessy', 2;
Output :