order is a reserved word in SQL

I'm not sure if this was ever mentioned in class or anything, but "order" is a reserved word in SQL, so you cannot (in MySQL, anyway) create a table named "order". The error I was getting when I was attempting to do this was not very clear, so it took a while to realize that this was the problem.

So, if any of you run into this, renaming the table to something like "customer_order" should work fine.

Firebird procedure example

Here is an example of how to write a procedure in Firebird using an existing relation called CUSTOMER

SET TERM ^ ;

CREATE PROCEDURE NEWCUSTNUM

RETURNS
 ( custNum2 integer, CUST_No integer, CUSTOMER varchar (25) )
AS

BEGIN
  custNum2 = 7000;
  for SELECT CUST_NO, CUSTOMER
FROM CUSTOMER
WHERE CUSTOMER.CUST_NO >=1000 AND CUSTOMER.CUST_NO <=1009

into :CUST_NO, :CUSTOMER

DO
begin
custNum2 = 7000 + :CUST_NO;
suspend;

END
end^
SET TERM ; ^

 

Homework 5

http://cs.winona.edu/faculty/nanderson/485-s10/hw5.pdf

Research Presentation

http://cs.winona.edu/faculty/nanderson/485-s10/ResearchPresentation.pdf

Stored Procedures Advantages

I found this in the book "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML" by Ken Henderson and thought it might be helpful to the class:

Stored Procedure Advantages
Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including

• Execution plan retention and reuse
• Query autoparameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwith conservation
• Support for automatic execution at system start-up

Stored Procedure Question

Can you create a stored procedure that uses more than one relation?

Homework 4

http://cs.winona.edu/faculty/nanderson/485-s10/HW4.pdf