How to Modify a Stored Procedure in SQL Server?
Last Updated :
17 May, 2024
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 :Â
Similar Reads
How to SELECT FROM Stored Procedure in SQL
Stored procedures are precompiled SQL queries stored in the database that encapsulate logic and can accept parameters, perform operations and return results. They are widely used in SQL for encapsulating reusable logic, improving performance and enhancing security. In this article, weâll explore how
4 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again.
2 min read
How to Limit Rows in a SQL Server?
To limit rows in SQL Server, use the TOP clause in the SELECT statement. Using the TOP clause in SQL Server, users can limit the number of rows in the results set. Here, we will understand how to limit rows in SQL Server with the help of different examples. Steps to Limit Rows in SQL ServerLet's che
3 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then
3 min read
How to Create and Call a Stored Procedure in SQL?
With this article, we will learn how to Create and Call a Stored Procedure in SQL. For this article, we are going to use MSSQL as our database server. What is a Stored Procedure?A stored procedure is a pre-written SQL query that can be called multiple times and will run as the same. Like we can crea
2 min read
How to Rename a View in SQL Server?
The view is a virtual table based on the result set of an SQL statement. It is like the subset of the table and created to optimize the database experience. Like a real table, this also contains rows and columns. The data in a view are extracted from one or more real tables in the database. Renaming
2 min read
SQL VS ADO.NET With Stored Procedures
Stored Procedures have been used as a solution for so many problems, performance issues and not being able to separate the business Logic and transaction management. Stored Procedure tries to solve this major problem while developing a website. In .NET when we want to create a website or desktop app
6 min read
How to Export Data to the .CSV File Using SQL Server Stored Procedure?
Exporting data from SQL Server to a CSV file is a common task when handling large datasets or sharing data with other applications. SQL Server Management Studio (SSMS) provides a straightforward way to export tables using its Import and Export Wizard. In this article, we will see, the process of exp
3 min read
SQL Query to Rename Stored Procedure
Stored Procedure is a saved SQL code. It is created to save time as we can use it again and again without writing the whole query. In this article, we will see how to Rename a Stored Procedure. To create a Procedure:Syntax: CREATE PROCEDURE procedure_name AS SELECT * FROM table_name;SQL query whose
2 min read
How to Drop Procedure in SQL
In SQL, stored procedures are used to encapsulate complex logic and queries. However, there may come a time when we need to remove or delete a stored procedure from a database. In this article, we will cover the various methods for dropping a stored procedure in SQL along with examples and explanati
3 min read