This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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!

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.

Posted by Andy Brown on 24 May 2012

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]

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

Parts of this 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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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