BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Stored procedures are programs in SQL which allow you to pass parameters to queries and manipulate sets of rows in tables. Find out how to write them with this online training blog!
- Stored Procedures in SQL
- Creating Simple Stored Procedures
- Running Stored Procedures
- Modifying or Changing a Stored Procedure (this blog)
- Passing Parameters to Stored Procedures
- A Worked Example of a Longer Stored Procedure
This blog is part of a complete SQL Server tutorial, and is also referenced from our ASP.NET online training blog.
Posted by Andy Brown on 24 May 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Modifying or Changing a Stored Procedure
What happens if you want to tweak your stored procedure? The odd answer (odd for any other software; for SQL Server it's completely normal) is that you write code to change it.
Starting to Modify a Procedure by Right-Clicking
The easiest way to start to modify a stored procedure is to right-click on it:
Right-click on the procedure that you want to change and choose to modify it as shown here.
This, however, produces a lot of irrelevant SQL:
USE [Movies]
GO
-- ***** Object: StoredProcedure [dbo].[spListActors]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spListActors]
AS
-- display list of actors
SELECT
ActorName,
ActorGender
FROM
tblActor
Out of this, the only bit that you need is the following code:
ALTER PROC [dbo].[spListActors]
AS
-- display list of actors
SELECT
ActorName,
ActorGender
FROM
tblActor
Modifying a Stored Procedure you've Just Written
The other way to change a procedure is just to change the word CREATE to ALTER. Suppose that you've just written and run this SQL:
CREATE PROC spListActors
AS
-- display list of actors
SELECT
ActorName,
ActorGender
FROM
tblActor
Then if you just change the word CREATE to ALTER, you'll be ready to modify your stored procedure.
What Can You Do when Modifying a Stored Procedure?
The answer to this question is: anything you could do when you first created it. Provided that the SQL you type in makes sense, you can slightly tweak your stored procedure or completely rewrite it. Here's an example of the former:
ALTER PROC spListActors
AS
-- display list of actors in age order
SELECT
ActorName,
ActorGender
FROM
tblActor
ORDER BY
ActorDOB
Now when you run the spListActors procedure in future, the actor names will appear sorted by their dates of birth.
- Stored Procedures in SQL
- Creating Simple Stored Procedures
- Running Stored Procedures
- Modifying or Changing a Stored Procedure (this blog)
- Passing Parameters to Stored Procedures
- A Worked Example of a Longer Stored Procedure