Freetutes.com

VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

VB .NET - Learn Visual Basic .NET

Systems Analysis - System analysis and Design tutorial for Software Engineering


You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Using Stored Procedures to Execute Statements on a Database

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


  

<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © Freetutes.com | All Rights Reserved