Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 2 of 2 total |
SP with parameters |
Thu, Apr 5 2007 6:59 AM | Permanent Link |
"Harry de Boer" | LS,
How do I use parameters in SP's. Given the SP (for adding records to a testtable): DECLARE Test CURSOR FOR stmt; DECLARE I INTEGER DEFAULT 1; DECLARE II INTEGER DEFAULT 10000; PREPARE stmt FROM 'SELECT * FROM Test'; OPEN Test; WHILE I <= II DO INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES (I, 'Name' + CAST(I AS VARCHAR(10)), CURRENT_TIMESTAMP); SET I=I+1; END WHILE; CLOSE Test; I want I and II to be replaced by parameters so I could add more records with different values each time I call the SP What are the steps to take (couldn't find it in the docs). Regards, Harry |
Thu, Apr 5 2007 7:55 AM | Permanent Link |
"Harry de Boer" | LS
Found it! Here's an example for those interested. The SP add records to a table where you can set the record id's (from-till) and some dates as parameter values. Tested with 1.02. Speed is excellent! PROCEDURE "addRecords" (IN "RECID1" INTEGER, IN "RECID2" INTEGER, IN "DAT1" TIMESTAMP, IN "DAT2" TIMESTAMP) BEGIN DECLARE Tbl CURSOR FOR stmt; DECLARE I INTEGER; DECLARE II INTEGER; SET I = RECID1; SET II = RECID2; PREPARE stmt FROM 'SELECT * FROM Tbl'; OPEN Tbl; WHILE I <= II DO INSERT INTO Tbl (veld1, veld2, veld3, datum1, datum2) VALUES (I, '_' + CAST(I AS VARCHAR(10)), CURRENT_TIMESTAMP, DAT1, DAT2); SET I=I+1; END WHILE; CLOSE Tbl; Regards, Harry "Harry de Boer" <harry@staaf.nl> schreef in bericht news:160B6962-AF84-4269-95DB-91D728FEB945@news.elevatesoft.com... > LS, > > How do I use parameters in SP's. Given the SP (for adding records to a > testtable): > > DECLARE Test CURSOR FOR stmt; > DECLARE I INTEGER DEFAULT 1; > DECLARE II INTEGER DEFAULT 10000; > PREPARE stmt FROM 'SELECT * FROM Test'; > OPEN Test; > WHILE I <= II DO > INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES (I, 'Name' + > CAST(I > AS VARCHAR(10)), CURRENT_TIMESTAMP); > SET I=I+1; > END WHILE; > CLOSE Test; > > I want I and II to be replaced by parameters so I could add more records > with different values each time I call the SP > What are the steps to take (couldn't find it in the docs). > > Regards, Harry > > |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |