Using SQL statements inside a stored procedure is different from using SQL directly in tools like solidDB SQL Editor (solsql) – a special syntax is required.
There are two ways to execute SQL statements inside a procedure:
▪ use EXECDIRECT syntax to execute a statement, see Using EXECDIRECT,
▪ treat the SQL statement as a "cursor", see Using a cursor.
You can also create a stored procedure that uses both EXECDIRECT syntax and cursors, for example:
"CREATE PROCEDURE p2 BEGIN -- This variable holds an ID that we insert into the table. DECLARE id INT; -- Here are simple examples of EXECDIRECT. EXEC SQL EXECDIRECT CREATE TABLE table1 (id_col INT); EXEC SQL EXECDIRECT INSERT INTO table1 (id_col) values (1); -- Here is an example of a cursor. EXEC SQL PREPARE cursor1 INSERT INTO table1 (id_col) values (?); id := 2; WHILE id <= 10 LOOP EXEC SQL EXECUTE cursor1 USING (id); id := id + 1; END LOOP; EXEC SQL CLOSE cursor1; EXEC SQL DROP cursor1; END";
Notes
▪ Any valid SQL statement can be used inside a stored procedure, including DDL and DML statements.
▪ All SQL statements must be preceded by the keywords EXEC SQL.