1
©2014, Thomas Janicki
SYSTEMS DESIGN / CAPSTONE PROJECT
MIS 413
User Guide 2.2
Creating Update, Insert and Delete Stored Procedures
This guide will demonstrate building Insert, Update and Delete Stored Procedures with a few
input parameters using the Query Editor Tool. They all assume you have a person table with
a personID (auto number), FN, LN, Email, PWD attributes.
Example A: Update Stored Procedure
The easiest manner to create a stored procedure is to let the wizard in SQL assist you.
1) Open SQL and the proper database
2) Expand the ‘programmability’ tab under your database
3) Right click on stored procedures and click ‘new stored procedure’
4) Consistent and proper naming of your stored procedure will help you and future
developers understand their purpose. A guideline for this class would be:
Name of the Table, the Operation (select, update, etc.), Optional Word(s) as in:
personUpdate
5) Add your name of the procedure (without the < > brackets) as in:
CREATE PROCEDURE personUpdate in the first row of the stored procedure.
6) To build your first UPDATE, use the Design in Query Editor Option. Delete the
following row in your Stored Procedure:
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
7) Then right click in the blank area and select DESIGN IN QUERY EDITOR
A dialog box will appear and select the name of the table to be updated. A general
rule is you will only UPDATE one table at a time. Do not try to UPDATE 2 or more
tables with one UPDATE statement. If you need to update more than one table, you
can do two separate UPDATE statements in the same Stored Procedure.
8) Right click in the space to the right of the table in your diagram and select the
CHANGE TYPE and then UPDATE option.
9) Select the Columns to be Updated, notice the Primary Key will be bolded; you
SHOULD NOT update the Primary Key as this will become the WHERE clause.