Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
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
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.
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.