SQL stored procedures and passing parameters
Part four of a six-part series of blogs

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!

  1. Stored Procedures in SQL
  2. Creating Simple Stored Procedures
  3. Running Stored Procedures
  4. Modifying or Changing a Stored Procedure (this blog)
  5. Passing Parameters to Stored Procedures
  6. 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:

Modifying a stored procedure

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.

  1. Stored Procedures in SQL
  2. Creating Simple Stored Procedures
  3. Running Stored Procedures
  4. Modifying or Changing a Stored Procedure (this blog)
  5. Passing Parameters to Stored Procedures
  6. A Worked Example of a Longer Stored Procedure
This blog has 0 threads Add post