This page has 0 threads | Add post

Modifying or Changing a Stored Procedure

Written by Andy Brown

In this tutorial

Starting to Modify a Procedure by Right-Clicking
Modifying a Stored Procedure you've Just Written
What Can You Do when Modifying 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]

-- ***** Object: StoredProcedure [dbo].[spListActors]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[spListActors]

-- 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]

-- 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

-- 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

-- 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.

Previous
tutorial

Trapping errors and debugging

Previous
part

Running Stored Procedures

Top of page

Next
part

Passing Parameters to Stored Procedures

Next
tutorial

User-defined functions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Stored procedures

Modifying or Changing a Stored Procedure

Starting to Modify a Procedure by Right-Clicking

Modifying a Stored Procedure you've Just Written

What Can You Do when Modifying a Stored Procedure?

Head office

London

Manchester