CREATE TABLE "Contact2"
(
"ContactID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"IsActive" BOOLEAN NOT NULL,
"Title" VARCHAR(20) COLLATE "UNI",
"GivenName" VARCHAR(30) COLLATE "UNI",
"FamilyName" VARCHAR(40) COLLATE "UNI",
"Address1" VARCHAR(50) COLLATE "UNI",
"Address2" VARCHAR(50) COLLATE "UNI",
"Address3" VARCHAR(50) COLLATE "UNI",
"City" VARCHAR(50) COLLATE "UNI",
CONSTRAINT "Contact_PK" PRIMARY KEY ("ContactID")
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!

INSERT INTO "Contact2" VALUES (1,
                  TRUE,
                  'Captain',
                  'Rhonda',
                  'Ricepaper',
                  'Apart 12',
                  '47 Berry Lane',
                  NULL,
                  'Springield')!

INSERT INTO "Contact2" VALUES (2,
                  TRUE,
                  'Doctor',
                  'Miles',
                  'Aparti',
                  'Suite 99',
                  'Plaza Place',
                  '99 Washington Street',
                  'Jonestown')!

INSERT INTO "Contact2" VALUES (3,
                  TRUE,
                  'Mr',
                  'Harry',
                  'Brown',
                  '21 Main Street',
                  NULL,
                  NULL,
                  'Paris')!
				  
-------------------------------------
CREATE PROCEDURE "SPGetContx2" ()
BEGIN
DECLARE Result CURSOR WITH RETURN For stmt;
DECLARE SQLStatement VARCHAR DEFAULT 'SELECT C.*,
 COALESCE(IF(C.Title <> '''', C.Title + '' '', null), '''') +
 COALESCE(IF(C.GivenName <> '''', C.GivenName + '' '', null), '''') +
 COALESCE(IF(C.FamilyName <> '''', C.FamilyName, null), '''') ContactN,
 COALESCE(IF(C.Address1 <> '''', C.Address1, null),'''')+
 COALESCE('', '' + IF(C.Address2 <> '''', C.Address2, null),'''')+
 COALESCE('', '' + IF(C.Address3 <> '''', C.Address3, null),'''') Addr1_3
 FROM Contact2 C';
 PREPARE Stmt FROM SQLStatement;
 OPEN Result;
END
VERSION 1.00!				     