SELECT * FROM MySchema.MyTable WHERE ColA > 100;
INSERT INTO MySchema.MyTable (ColA,ColB) VALUES (50, 'hi');

... 
/* Typically complex criteria would be formed based upon inputs to the procedure. 
 In this simple example the criteria is references the using clause to isolate 
 the SQL string from referencing a value from the procedure directly */ 
DECLARE string criteria = 'Customer.Accounts.Last = DVARS.LastName'; 
/* Now we create the desired SQL string */ 
DECLARE string sql_string = 'SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria; 
/* The execution of the SQL string will create the #temp table with the columns (ID, Name, Birthdate). 
  Note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. */ 
EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; 
/* The temp table can now be used with the values from the Dynamic SQL */ 
loop on (SELCT ID from #temp) as myCursor 
...

...
DECLARE string crit = null; 
IF (AccountAccess.GetAccounts.ID IS NOT NULL) 
 crit = ‘(Customer.Accounts.ID = DVARS.ID)’; 
ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL) 
BEGIN 
 IF (AccountAccess.GetAccounts.LastName == ‘%’) 
   ERROR "Last name cannot be %"; 
 ELSE IF (LOCATE(‘%’, AccountAccess.GetAccounts.LastName) < 0) 
   crit = ‘(Customer.Accounts.Last = DVARS.LastName)’; 
 ELSE 
   crit = ‘(Customer.Accounts.Last LIKE DVARS.LastName)’; 
 IF (AccountAccess.GetAccounts.bday IS NOT NULL) 
   crit = ‘(‘ || crit || ‘ and (Customer.Accounts.Birthdate = DVARS.BirthDay))’; 
END 
ELSE 
 ERROR "ID or LastName must be specified."; 
EXECUTE IMMEDIATE ‘SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ’ || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday;
...

IF ( var1 = 'North America') 
BEGIN 
  ...statement... 
END ELSE 
BEGIN 
  ...statement... 
END

ERROR 'Invalid input value: ' || nvl(Acct.GetBalance.AcctID, 'null');

CREATE VIRTUAL PROCEDURE 
BEGIN 
  MySchema.GetBalance.RetVal = UPPER(MySchema.GetBalance.AcctID);
  SELECT Balance FROM MySchema.Accts WHERE MySchema.Accts.AccountID = MySchema.GetBalance.AcctID; 
END

CREATE VIRTUAL PROCEDURE
BEGIN
  DECLARE double total;
  DECLARE integer transactions;
  LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor
  BEGIN
    IF(txncursor.type <> 'Sale')
    BEGIN
      CONTINUE;
    END ELSE 
    BEGIN
      total = (total + txncursor.amt);
      transactions = (transactions + 1);
      IF(transactions = 100)
      BEGIN
        BREAK;
      END
    END
  END
  SELECT total, (total / transactions) AS avg_transaction;
END
        

CREATE VIRTUAL PROCEDURE 
BEGIN 
  DECLARE string VARIABLES.SORTDIRECTION; 
  VARIABLES.SORTDIRECTION = PartsVirtual.OrderedQtyProc.SORTMODE; 
  IF ( ucase(VARIABLES.SORTDIRECTION) = 'ASC' ) 
  BEGIN 
    SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID; 
  END ELSE 
  BEGIN 
    SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID DESC;
  END
END