Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Modifying an existing SQL Server stored procedure


When you first create your stored procedures it may work as planned, but how to do you modify an existing stored procedure.  In this topic we look at the ALTER PROCEDURE command and it is used.


Modifying or ALTERing a stored procedure is pretty simple.  Once a stored procedure has been created it is stored within one of the system tables in the database that is was created in.  When you modify a stored procedure the entry that was originally made in the system table is replaced by this new code.  Also, SQL Server will recompile the stored procedure the next time it is run, so your users are using the new logic.  The command to modify an existing stored procedure is ALTER PROCEDURE or ALTER PROC.

Modifying an Existing Stored Procedure

Let's say we have the following existing stored procedure:  This allows us to do an exact match on the City.

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
FROM Person.Address
WHERE City = @City

Let's say we want to change this to do a LIKE instead of an equals.

To change the stored procedure and save the updated code you would use the ALTER PROCEDURE command as follows.

ALTER PROCEDURE dbo.uspGetAddress @City nvarchar(30)
FROM Person.Address
WHERE City LIKE @City + '%'

Now the next time that the stored procedure is called by an end user it will use this new logic.

Last Update: 4/1/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Sunday, March 03, 2019 - 7:09:19 AM - Greg Robidoux Back To Top

Hi Phlip,

take a look at this page that shows how to create a procedure with multiple parameters.



Saturday, March 02, 2019 - 8:22:07 AM - Philip van Gass Back To Top

 Hi Greg.

This example is very simple. But what do you do if you want to change the procedure so as to include an extra parameter like @addressline ? 

Learn more about SQL Server tools