Create a New Database From an SQL Script

The following method will create a new database from an SQL script. It uses the embedded Firebird but you can switch to a standalone server by changing the connection string. It requires Firebird ADO.NET Provider 1.7a.

private static void CreateEmbeddedDb(string pathDb, string pathScript)
{
	// construct the connection string
	FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
	csb.ServerType = 1; // we are using the embedded Firebird
	csb.Database = pathDb;

	if (File.Exists(csb.Database)) 
	{
		throw new ApplicationException("The database file does already exist.");
	}

	// create a new database
	FbConnection.CreateDatabase(csb.ToString());
	
	// parse the SQL script
	FbScript script = new FbScript(pathScript);
	script.Parse();
	
	// execute the SQL script
	using(FbConnection c = new FbConnection(csb.ToString()))
	{
		FbBatchExecution fbe = new FbBatchExecution(c);
		foreach (string cmd in script.Results) 
		{
			fbe.SqlStatements.Add(cmd);
		}
		fbe.Execute();
	}
}

Example SQL script - employee.sql

You can test it with the following script - it creates an example employee.fdb database (that one which ships with Firebird).

/******************************************************************************/
/****                               Domains                                ****/
/******************************************************************************/

CREATE DOMAIN ADDRESSLINE AS
VARCHAR(30) CHARACTER SET NONE;

CREATE DOMAIN BUDGET AS
DECIMAL(12,2)
DEFAULT 50000
CHECK (VALUE > 10000 AND VALUE <= 2000000);

CREATE DOMAIN COUNTRYNAME AS
VARCHAR(15) CHARACTER SET NONE;

CREATE DOMAIN CUSTNO AS
INTEGER
CHECK (VALUE > 1000);

CREATE DOMAIN DEPTNO AS
CHAR(3) CHARACTER SET NONE
CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);

CREATE DOMAIN EMPNO AS
SMALLINT;

CREATE DOMAIN FIRSTNAME AS
VARCHAR(15) CHARACTER SET NONE;

CREATE DOMAIN JOBCODE AS
VARCHAR(5) CHARACTER SET NONE
CHECK (VALUE > '99999');

CREATE DOMAIN JOBGRADE AS
SMALLINT
CHECK (VALUE BETWEEN 0 AND 6);

CREATE DOMAIN LASTNAME AS
VARCHAR(20) CHARACTER SET NONE;

CREATE DOMAIN PHONENUMBER AS
VARCHAR(20) CHARACTER SET NONE;

CREATE DOMAIN PONUMBER AS
CHAR(8) CHARACTER SET NONE
CHECK (VALUE STARTING WITH 'V');

CREATE DOMAIN PRODTYPE AS
VARCHAR(12) CHARACTER SET NONE
DEFAULT 'software'
NOT NULL
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));

CREATE DOMAIN PROJNO AS
CHAR(5) CHARACTER SET NONE
CHECK (VALUE = UPPER (VALUE));

CREATE DOMAIN SALARY AS
NUMERIC(10,2)
DEFAULT 0
CHECK (VALUE > 0);



/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR CUST_NO_GEN;
CREATE GENERATOR EMP_NO_GEN;


/******************************************************************************/
/****                              Exceptions                              ****/
/******************************************************************************/

CREATE EXCEPTION CUSTOMER_CHECK 'Overdue balance -- can not ship.';

CREATE EXCEPTION CUSTOMER_ON_HOLD 'This customer is on hold.';

CREATE EXCEPTION ORDER_ALREADY_SHIPPED 'Order status is "shipped."';

CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records before deleting this employee.';

CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';



SET TERM ^ ; 



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/

CREATE PROCEDURE ADD_EMP_PROJ (
    EMP_NO SMALLINT,
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE ALL_LANGS
RETURNS (
    CODE VARCHAR(5) CHARACTER SET NONE,
    GRADE VARCHAR(5) CHARACTER SET NONE,
    COUNTRY VARCHAR(15) CHARACTER SET NONE,
    LANG VARCHAR(15) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE DELETE_EMPLOYEE (
    EMP_NUM INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE DEPT_BUDGET (
    DNO CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT DECIMAL(12,2))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE MAIL_LABEL (
    CUST_NO INTEGER)
RETURNS (
    LINE1 CHAR(40) CHARACTER SET NONE,
    LINE2 CHAR(40) CHARACTER SET NONE,
    LINE3 CHAR(40) CHARACTER SET NONE,
    LINE4 CHAR(40) CHARACTER SET NONE,
    LINE5 CHAR(40) CHARACTER SET NONE,
    LINE6 CHAR(40) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE ORG_CHART
RETURNS (
    HEAD_DEPT CHAR(25) CHARACTER SET NONE,
    DEPARTMENT CHAR(25) CHARACTER SET NONE,
    MNGR_NAME CHAR(20) CHARACTER SET NONE,
    TITLE CHAR(5) CHARACTER SET NONE,
    EMP_CNT INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SHOW_LANGS (
    CODE VARCHAR(5) CHARACTER SET NONE,
    GRADE SMALLINT,
    CTY VARCHAR(15) CHARACTER SET NONE)
RETURNS (
    LANGUAGES VARCHAR(15) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SUB_TOT_BUDGET (
    HEAD_DEPT CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT_BUDGET DECIMAL(12,2),
    AVG_BUDGET DECIMAL(12,2),
    MIN_BUDGET DECIMAL(12,2),
    MAX_BUDGET DECIMAL(12,2))
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE COUNTRY (
    COUNTRY   COUNTRYNAME NOT NULL,
    CURRENCY  VARCHAR(10) CHARACTER SET NONE NOT NULL
);


CREATE TABLE CUSTOMER (
    CUST_NO         CUSTNO NOT NULL,
    CUSTOMER        VARCHAR(25) CHARACTER SET NONE NOT NULL,
    CONTACT_FIRST   FIRSTNAME,
    CONTACT_LAST    LASTNAME,
    PHONE_NO        PHONENUMBER,
    ADDRESS_LINE1   ADDRESSLINE,
    ADDRESS_LINE2   ADDRESSLINE,
    CITY            VARCHAR(25) CHARACTER SET NONE,
    STATE_PROVINCE  VARCHAR(15) CHARACTER SET NONE,
    COUNTRY         COUNTRYNAME,
    POSTAL_CODE     VARCHAR(12) CHARACTER SET NONE,
    ON_HOLD         CHAR(1) CHARACTER SET NONE DEFAULT NULL
);


CREATE TABLE DEPARTMENT (
    DEPT_NO     DEPTNO NOT NULL,
    DEPARTMENT  VARCHAR(25) CHARACTER SET NONE NOT NULL,
    HEAD_DEPT   DEPTNO,
    MNGR_NO     EMPNO,
    BUDGET      BUDGET,
    LOCATION    VARCHAR(15) CHARACTER SET NONE,
    PHONE_NO    PHONENUMBER DEFAULT '555-1234'
);


CREATE TABLE EMPLOYEE (
    EMP_NO       EMPNO NOT NULL,
    FIRST_NAME   FIRSTNAME NOT NULL,
    LAST_NAME    LASTNAME NOT NULL,
    PHONE_EXT    VARCHAR(4) CHARACTER SET NONE,
    HIRE_DATE    TIMESTAMP DEFAULT 'NOW' NOT NULL,
    DEPT_NO      DEPTNO NOT NULL,
    JOB_CODE     JOBCODE NOT NULL,
    JOB_GRADE    JOBGRADE NOT NULL,
    JOB_COUNTRY  COUNTRYNAME NOT NULL,
    SALARY       SALARY NOT NULL,
    FULL_NAME    COMPUTED BY (last_name || ', ' || first_name)
);


CREATE TABLE EMPLOYEE_PROJECT (
    EMP_NO   EMPNO NOT NULL,
    PROJ_ID  PROJNO NOT NULL
);


CREATE TABLE JOB (
    JOB_CODE         JOBCODE NOT NULL,
    JOB_GRADE        JOBGRADE NOT NULL,
    JOB_COUNTRY      COUNTRYNAME NOT NULL,
    JOB_TITLE        VARCHAR(25) CHARACTER SET NONE NOT NULL,
    MIN_SALARY       SALARY NOT NULL,
    MAX_SALARY       SALARY NOT NULL,
    JOB_REQUIREMENT  BLOB SUB_TYPE 1 SEGMENT SIZE 400,
    LANGUAGE_REQ     VARCHAR(15) [1:5] CHARACTER SET NONE
);


CREATE TABLE PROJ_DEPT_BUDGET (
    FISCAL_YEAR       INTEGER NOT NULL,
    PROJ_ID           PROJNO NOT NULL,
    DEPT_NO           DEPTNO NOT NULL,
    QUART_HEAD_CNT    INTEGER [1:4],
    PROJECTED_BUDGET  BUDGET
);


CREATE TABLE PROJECT (
    PROJ_ID      PROJNO NOT NULL,
    PROJ_NAME    VARCHAR(20) CHARACTER SET NONE NOT NULL,
    PROJ_DESC    BLOB SUB_TYPE 1 SEGMENT SIZE 800,
    TEAM_LEADER  EMPNO,
    PRODUCT      PRODTYPE
);


CREATE TABLE SALARY_HISTORY (
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) CHARACTER SET NONE NOT NULL,
    OLD_SALARY      SALARY NOT NULL,
    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
    NEW_SALARY      COMPUTED BY (old_salary + old_salary * percent_change / 100)
);


CREATE TABLE SALES (
    PO_NUMBER     PONUMBER NOT NULL,
    CUST_NO       CUSTNO NOT NULL,
    SALES_REP     EMPNO,
    ORDER_STATUS  VARCHAR(7) CHARACTER SET NONE DEFAULT 'new' NOT NULL,
    ORDER_DATE    TIMESTAMP DEFAULT 'NOW' NOT NULL,
    SHIP_DATE     TIMESTAMP,
    DATE_NEEDED   TIMESTAMP,
    PAID          CHAR(1) CHARACTER SET NONE DEFAULT 'n',
    QTY_ORDERED   INTEGER DEFAULT 1 NOT NULL,
    TOTAL_VALUE   DECIMAL(9,2) NOT NULL,
    DISCOUNT      FLOAT DEFAULT 0 NOT NULL,
    ITEM_TYPE     PRODTYPE,
    AGED          COMPUTED BY (ship_date - order_date)
);




/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/


/* View: PHONE_LIST */
CREATE VIEW PHONE_LIST(
    EMP_NO,
    FIRST_NAME,
    LAST_NAME,
    PHONE_EXT,
    LOCATION,
    PHONE_NO)
AS
SELECT
    emp_no, first_name, last_name, phone_ext, location, phone_no
    FROM employee, department
    WHERE employee.dept_no = department.dept_no
;




/* Check constraints definition */

ALTER TABLE JOB ADD CHECK (min_salary < max_salary);
ALTER TABLE EMPLOYEE ADD CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country));
ALTER TABLE PROJ_DEPT_BUDGET ADD CHECK (FISCAL_YEAR >= 1993);
ALTER TABLE SALARY_HISTORY ADD CHECK (percent_change between -50 and 50);
ALTER TABLE CUSTOMER ADD CHECK (on_hold IS NULL OR on_hold = '*');
ALTER TABLE SALES ADD CHECK (order_status in
                            ('new', 'open', 'shipped', 'waiting'));
ALTER TABLE SALES ADD CHECK (ship_date >= order_date OR ship_date IS NULL);
ALTER TABLE SALES ADD CHECK (date_needed > order_date OR date_needed IS NULL);
ALTER TABLE SALES ADD CHECK (paid in ('y', 'n'));
ALTER TABLE SALES ADD CHECK (qty_ordered >= 1);
ALTER TABLE SALES ADD CHECK (total_value >= 0);
ALTER TABLE SALES ADD CHECK (discount >= 0 AND discount <= 1);
ALTER TABLE SALES ADD CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL));
ALTER TABLE SALES ADD CHECK (NOT (order_status = 'shipped' AND
            EXISTS (SELECT on_hold FROM customer
                    WHERE customer.cust_no = sales.cust_no
                    AND customer.on_hold = '*')));


/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE DEPARTMENT ADD UNIQUE (DEPARTMENT);
ALTER TABLE PROJECT ADD UNIQUE (PROJ_NAME);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE COUNTRY ADD PRIMARY KEY (COUNTRY);
ALTER TABLE CUSTOMER ADD PRIMARY KEY (CUST_NO);
ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DEPT_NO);
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD PRIMARY KEY (EMP_NO, PROJ_ID);
ALTER TABLE JOB ADD PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
ALTER TABLE PROJECT ADD PRIMARY KEY (PROJ_ID);
ALTER TABLE PROJ_DEPT_BUDGET ADD PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_NO);
ALTER TABLE SALARY_HISTORY ADD PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID);
ALTER TABLE SALES ADD PRIMARY KEY (PO_NUMBER);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MNGR_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY) REFERENCES JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE JOB ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE SALARY_HISTORY ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE SALES ADD FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER (CUST_NO);
ALTER TABLE SALES ADD FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE (EMP_NO);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX CUSTNAMEX ON CUSTOMER (CUSTOMER);
CREATE INDEX CUSTREGION ON CUSTOMER (COUNTRY, CITY);
CREATE DESCENDING INDEX BUDGETX ON DEPARTMENT (BUDGET);
CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);
CREATE DESCENDING INDEX MAXSALX ON JOB (JOB_COUNTRY, MAX_SALARY);
CREATE INDEX MINSALX ON JOB (JOB_COUNTRY, MIN_SALARY);
CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);
CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);
CREATE INDEX UPDATERX ON SALARY_HISTORY (UPDATER_ID);
CREATE INDEX NEEDX ON SALES (DATE_NEEDED);
CREATE DESCENDING INDEX QTYX ON SALES (ITEM_TYPE, QTY_ORDERED);
CREATE INDEX SALESTATX ON SALES (ORDER_STATUS, PAID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: POST_NEW_ORDER */
CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
    POST_EVENT 'new_order';
END^


/* Trigger: SAVE_SALARY_CHANGE */
CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
    IF (old.salary <> new.salary) THEN
        INSERT INTO salary_history
            (emp_no, change_date, updater_id, old_salary, percent_change)
        VALUES (
            old.emp_no,
            'NOW',
            user,
            old.salary,
            (new.salary - old.salary) * 100 / old.salary);
END^


/* Trigger: SET_CUST_NO */
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    /* FIXED by helebor 19.01.2004 */
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END^


/* Trigger: SET_EMP_NO */
CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    /* FIXED by helebor 19.01.2004 */
    if (new.emp_no is null) then
    new.emp_no = gen_id(emp_no_gen, 1);
END^


SET TERM ; ^



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE ADD_EMP_PROJ (
    EMP_NO SMALLINT,
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
 BEGIN
 INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
 WHEN SQLCODE -530 DO
  EXCEPTION unknown_emp_id;
 END
 SUSPEND;
END
^

ALTER PROCEDURE ALL_LANGS
RETURNS (
    CODE VARCHAR(5) CHARACTER SET NONE,
    GRADE VARCHAR(5) CHARACTER SET NONE,
    COUNTRY VARCHAR(15) CHARACTER SET NONE,
    LANG VARCHAR(15) CHARACTER SET NONE)
AS
    BEGIN
 FOR SELECT job_code, job_grade, job_country FROM job 
  INTO :code, :grade, :country

 DO
 BEGIN
     FOR SELECT languages FROM show_langs 
       (:code, :grade, :country) INTO :lang DO
         SUSPEND;
     /* Put nice separators between rows */
     code = '=====';
     grade = '=====';
     country = '===============';
     lang = '==============';
     SUSPEND;
 END
    END
^

ALTER PROCEDURE DELETE_EMPLOYEE (
    EMP_NUM INTEGER)
AS
 DECLARE VARIABLE any_sales INTEGER;
BEGIN
 any_sales = 0;

 /*
  * If there are any sales records referencing this employee,
  * can't delete the employee until the sales are re-assigned
  * to another employee or changed to NULL.
  */
 SELECT count(po_number)
 FROM sales
 WHERE sales_rep = :emp_num
 INTO :any_sales;

 IF (any_sales > 0) THEN
 BEGIN
  EXCEPTION reassign_sales;
  SUSPEND;
 END

 /*
  * If the employee is a manager, update the department.
  */
 UPDATE department
 SET mngr_no = NULL
 WHERE mngr_no = :emp_num;

 /*
  * If the employee is a project leader, update project.
  */
 UPDATE project
 SET team_leader = NULL
 WHERE team_leader = :emp_num;

 /*
  * Delete the employee from any projects.
  */
 DELETE FROM employee_project
 WHERE emp_no = :emp_num;

 /*
  * Delete old salary records.
  */
 DELETE FROM salary_history
 WHERE emp_no = :emp_num;

 /*
  * Delete the employee.
  */
 DELETE FROM employee
 WHERE emp_no = :emp_num;

 SUSPEND;
END
^

ALTER PROCEDURE DEPT_BUDGET (
    DNO CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT DECIMAL(12,2))
AS
 DECLARE VARIABLE sumb DECIMAL(12, 2);
 DECLARE VARIABLE rdno CHAR(3);
 DECLARE VARIABLE cnt INTEGER;
BEGIN
 tot = 0;

 SELECT budget FROM department WHERE dept_no = :dno INTO :tot;

 SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;

 IF (cnt = 0) THEN
  SUSPEND;

 FOR SELECT dept_no
  FROM department
  WHERE head_dept = :dno
  INTO :rdno
 DO
  BEGIN
   EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;
   tot = tot + sumb;
  END

 SUSPEND;
END
^

ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
 FOR SELECT proj_id
  FROM employee_project
  WHERE emp_no = :emp_no
  INTO :proj_id
 DO
  SUSPEND;
END
^

ALTER PROCEDURE MAIL_LABEL (
    CUST_NO INTEGER)
RETURNS (
    LINE1 CHAR(40) CHARACTER SET NONE,
    LINE2 CHAR(40) CHARACTER SET NONE,
    LINE3 CHAR(40) CHARACTER SET NONE,
    LINE4 CHAR(40) CHARACTER SET NONE,
    LINE5 CHAR(40) CHARACTER SET NONE,
    LINE6 CHAR(40) CHARACTER SET NONE)
AS
 DECLARE VARIABLE customer VARCHAR(25);
 DECLARE VARIABLE first_name  VARCHAR(15);
 DECLARE VARIABLE last_name  VARCHAR(20);
 DECLARE VARIABLE addr1  VARCHAR(30);
 DECLARE VARIABLE addr2  VARCHAR(30);
 DECLARE VARIABLE city  VARCHAR(25);
 DECLARE VARIABLE state  VARCHAR(15);
 DECLARE VARIABLE country VARCHAR(15);
 DECLARE VARIABLE postcode VARCHAR(12);
 DECLARE VARIABLE cnt  INTEGER;
BEGIN
 line1 = '';
 line2 = '';
 line3 = '';
 line4 = '';
 line5 = '';
 line6 = '';

 SELECT customer, contact_first, contact_last, address_line1,
  address_line2, city, state_province, country, postal_code
 FROM CUSTOMER
 WHERE cust_no = :cust_no
 INTO :customer, :first_name, :last_name, :addr1, :addr2,
  :city, :state, :country, :postcode;

 IF (customer IS NOT NULL) THEN
  line1 = customer;
 IF (first_name IS NOT NULL) THEN
  line2 = first_name || ' ' || last_name;
 ELSE
  line2 = last_name;
 IF (addr1 IS NOT NULL) THEN
  line3 = addr1;
 IF (addr2 IS NOT NULL) THEN
  line4 = addr2;

 IF (country = 'USA') THEN
 BEGIN
  IF (city IS NOT NULL) THEN
   line5 = city || ', ' || state || '  ' || postcode;
  ELSE
   line5 = state || '  ' || postcode;
 END
 ELSE
 BEGIN
  IF (city IS NOT NULL) THEN
   line5 = city || ', ' || state;
  ELSE
   line5 = state;
  line6 = country || '    ' || postcode;
 END

 SUSPEND;
END
^

ALTER PROCEDURE ORG_CHART
RETURNS (
    HEAD_DEPT CHAR(25) CHARACTER SET NONE,
    DEPARTMENT CHAR(25) CHARACTER SET NONE,
    MNGR_NAME CHAR(20) CHARACTER SET NONE,
    TITLE CHAR(5) CHARACTER SET NONE,
    EMP_CNT INTEGER)
AS
 DECLARE VARIABLE mngr_no INTEGER;
 DECLARE VARIABLE dno CHAR(3);
BEGIN
 FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
  FROM department d
  LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
  ORDER BY d.dept_no
  INTO :head_dept, :department, :mngr_no, :dno
 DO
 BEGIN
  IF (:mngr_no IS NULL) THEN
  BEGIN
   mngr_name = '-TBH-';
   title = '';
  END

  ELSE
   SELECT full_name, job_code
   FROM employee
   WHERE emp_no = :mngr_no
   INTO :mngr_name, :title;

  SELECT COUNT(emp_no)
  FROM employee
  WHERE dept_no = :dno
  INTO :emp_cnt;

  SUSPEND;
 END
END
^

ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8) CHARACTER SET NONE)
AS
 DECLARE VARIABLE ord_stat CHAR(7);
 DECLARE VARIABLE hold_stat CHAR(1);
 DECLARE VARIABLE cust_no INTEGER;
 DECLARE VARIABLE any_po CHAR(8);
BEGIN
 SELECT s.order_status, c.on_hold, c.cust_no
 FROM sales s, customer c
 WHERE po_number = :po_num
 AND s.cust_no = c.cust_no
 INTO :ord_stat, :hold_stat, :cust_no;

 /* This purchase order has been already shipped. */
 IF (ord_stat = 'shipped') THEN
 BEGIN
  EXCEPTION order_already_shipped;
  SUSPEND;
 END

 /* Customer is on hold. */
 ELSE IF (hold_stat = '*') THEN
 BEGIN
  EXCEPTION customer_on_hold;
  SUSPEND;
 END

 /*
  * If there is an unpaid balance on orders shipped over 2 months ago,
  * put the customer on hold.
  */
 FOR SELECT po_number
  FROM sales
  WHERE cust_no = :cust_no
  AND order_status = 'shipped'
  AND paid = 'n'
  AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
  INTO :any_po
 DO
 BEGIN
  EXCEPTION customer_check;

  UPDATE customer
  SET on_hold = '*'
  WHERE cust_no = :cust_no;

  SUSPEND;
 END

 /*
  * Ship the order.
  */
 UPDATE sales
 SET order_status = 'shipped', ship_date = 'NOW'
 WHERE po_number = :po_num;

 SUSPEND;
END
^

ALTER PROCEDURE SHOW_LANGS (
    CODE VARCHAR(5) CHARACTER SET NONE,
    GRADE SMALLINT,
    CTY VARCHAR(15) CHARACTER SET NONE)
RETURNS (
    LANGUAGES VARCHAR(15) CHARACTER SET NONE)
AS
DECLARE VARIABLE i INTEGER;
BEGIN
  i = 1;
  WHILE (i <= 5) DO
  BEGIN
    SELECT language_req[:i] FROM joB
    WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty)
           AND (language_req IS NOT NULL))
    INTO :languages;
    IF (languages = ' ') THEN  /* Prints 'NULL' instead of blanks */
       languages = 'NULL';         
    i = i +1;
    SUSPEND;
  END
END
^

ALTER PROCEDURE SUB_TOT_BUDGET (
    HEAD_DEPT CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT_BUDGET DECIMAL(12,2),
    AVG_BUDGET DECIMAL(12,2),
    MIN_BUDGET DECIMAL(12,2),
    MAX_BUDGET DECIMAL(12,2))
AS
BEGIN
 SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
  FROM department
  WHERE head_dept = :head_dept
  INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
 SUSPEND;
END
^

SET TERM ; ^