You can create stored procedures to do most data maintenance chores such as adding
and deleting records or updating individual fields in existing records.
Each of these types of stored procedure is based on a type of SQL statement,
as described in the following list:
The following sections describe each type of SQL statement in more detail and
explain how to use stored procedures based on each type of SQL statement to implement cursorless
processes from ADO.
INSERT Statements in SQL
The SQL INSERT statement adds new records to a table. The INSERT statement
has this general format:
INSERT tablename
[(field list...)]
VALUES
(value list...)
where tablename is the name of a table in the current database, field list
is a comma-separated list of field names in the table, and value list is a comma-separated
list of values to assign to each field. The entries in the value list must match up in order,
number, and type with the entries in the field list.
Note that the field list is optional. If you leave it out of the INSERT statement,
however, you must supply a value for every field in the table in the value list, and the values
must be listed in the same order as the fields are listed in the original table structure.
A simple example of an INSERT statement might be this:
INSERT employees
(LastName, FirstName, HireDate)
VALUES
("Brunner", "Melanie",
#7/15/98#)
This would insert a record for Melanie Brunner with a hire date of July 15,
1998 into the employee table.
Listing 9.9 shows an example of a stored procedure that uses an INSERT statement.
LISTING 9.9
A STORED PROCEDURE BASED ON AN INSERT STATEMENT
create procedure insert_titles
@id varchar(6),
@title varchar(80),
@PubID varchar(4)
AS
INSERT titles
(title_id, title, Pub_ID)
VALUES
(@id, @title, @PubID)
GO
UPDATE Statements in SQL
The SQL UPDATE statement changes the values in one or more fields in designated
rows in a table:
UPDATE tablename
SET fieldname = expression[,...]
[WHERE condition]
where tablename is a valid table name for the current database, fieldname is
a valid field name in that table, expression is a valid expression that gives a value appropriate
for the field, and condition is an expression to filter rows. As the ellipses imply, you can
list modification statements for more than one field. Just separate each modification clause
from the others with commas.
A simple example of an UPDATE statement is this:
UPDATE employees
SET salary = salary * 1.05
WHERE employeeid = 432
This would give Employee #432 a five percent raise.
Note that the WHERE clause is optional (as it always is in SQL). If you leave
the WHERE clause out of an UPDATE statement, you will update the designated fields in all
the rows in the table. Listing 9.10 provides an example of a stored procedure based on an
UPDATE statement.
LISTING 9.10
A STORED PROCEDURE BASED ON AN UPDATE STATEMENT
create procedure update_titles_title
@id varchar(6),
@title varchar(80)
AS
UPDATE titles SET title = @title WHERE title_id
= @id
GO
DELETE Statements in SQL
The SQL DELETE statement removes rows from a table. This statement has this general
format:
DELETE tablename
[WHERE condition]
where tablename is a valid table name from the current database, and condition
is any valid record selection criterion. Note that the WHERE clause is optional (as it always
is in SQL). If you leave the WHERE clause out of a DELETE statement, you will delete all the
rows in the table.
A simple example of a DELETE statement is this:
DELETE from employee where employeeid = 231
This would remove the record for Employee #231 from the table. Listing 9.11
shows an example of a stored procedure created from a DELETE statement.
LISTING 9.11
A STORED PROCEDURE BASED ON A DELETE STATEMENT
create procedure delete_titles_by_id
@id varchar(6)
AS
DELETE titles
WHERE title_id = @id
GO
