Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to modify a stored procedure in MS SQL Server – Querychat

Stored procedures are essential objects when it comes to performing DML operations on an application level. We often need to modify previously created procedures, either due to database or business rules modifications. This article elaborates two ways of modifying a stored procedure in SQL Server: via UI and SQL code editor of SSMS.

What are the commands to modify a stored procedure?

There are two commands that we can use if we want to modify a stored procedure. The first version is the one that is most frequently used, although the second one is also correct and can be used as well.

Version 1:

ALTER PROCEDURE <database_schema_name>.<procedure_name> 

<parameter1 type>, 

<parameter2 type>,

 …

<parameterN type>

AS {

 [ BEGIN ] 

      sql_statement [;] 

      [ ...n ] 

 [ END ] 

} [;]

Version 2:

ALTER PROC <database_schema_name>.<procedure_name> 

<parameter1 type>, 

<parameter2 type>,

 …

<parameterN type>

AS {

 [ BEGIN ] 

      sql_statement [;] 

      [ ...n ] 

 [ END ] 

} [;]

The explanation of the arguments is the following:

  • <database_schema_name> – the name of the database schema which the procedure belongs to. This argument is optional, meaning that it can be omitted in case a database contains only one default database schema (dbo).
  • <procedure_name> – name of the procedure. This argument is required.
  • <parameter1 type>, <parameter2 type>, … , <parameterN type> – names and types of procedure’s parameter, where the number of parameters can vary from 0 to N. This argument is optional, meaning that it can be omitted in case the procedure doesn’t have a parameter defined.

How do we modify a stored procedure?

If we want to modify, alter, or edit a stored procedure within SQL Server, we can do it in a very simple way.

Let’s say we already have a stored procedure in our database that extracts the data from a table named Students. This procedure is called GetStudents and is located in the Stored Procedures folder within the database.

Figure 1. Locating the GetStudents procedure

As we can see, the procedure at the moment doesn’t have any parameters defined. If we want to see its code, we right-click on the procedure and select the “Modify” option.

Figure 2. Modifying the GetStudents procedure

 The code displayed in the code editor is the code of the procedure:

USE [test]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[GetStudents]

AS

BEGIN

 

SELECT *

FROM Students

END

Let’s execute the procedure and see what it gets as a result. Run the following code line in the SQL editor by copying the code and pressing F5:

EXEC GetStudents

We will get the following result of a procedure execution:

xFigure 3. Results of the GetStudents procedure execution

Now, let’s say we want to get students only of a particular department that we want to define at the time of the procedure’s execution. This requirement needs a procedure’s modification in terms of defining the parameter on the procedure.

Altering a stored procedure

We can easily add the parameter if we open the procedure as described above.

After we open the procedure, we manually change the code of the procedure. This change requires two steps to be done:

  1. Define the parameter within the definition of the procedure
  2. Use the parameter within the SQL query that is placed inside the body of the procedure

Step 1. We define the parameter of the procedure by changing the definition of the procedure to the following:

ALTER PROCEDURE [dbo].[GetStudents]

@departmentID SMALLINT

Step 2. We define the departmentId filter in the SQL query by adding the WHERE clause to the query:

SELECT *

FROM Students

WHERE departmentId=@departmentID

Step 3. The code displayed below is the complete code for altering the procedure:

ALTER PROCEDURE [dbo].[GetStudents]

@departmentID SMALLINT

AS

BEGIN

              SELECT *

              FROM Students

              WHERE departmentId=@departmentID

END

Step 4. After these modifications, press F5 to execute and save the modifications.

Step 5. Run the procedure by defining the parameter value. Let’s say we want to extract all students that have departmentId column value equal to 1. Run the following code line in the SQL editor by copying the code and pressing F5:

EXEC GetStudents 1

We will get the following result of a procedure execution:

Figure 4. Results of the parametrized GetStudents procedure execution

As we can see, now don’t get all rows from the Students table, but only the ones that have departmentId column value equal to 1. This means that our modification on the procedure works.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc