Calling stored procedures with supported databases / Stored procedure call with Microsoft SQL Server |
SQL Server stored procedures must be called with the input and output parameters specification in the USING clause of the EXECUTE, OPEN or FOREACH instruction. As in normal dynamic SQL, parameters must correspond by position and the IN/OUT/INOUT options must match the parameter definition of the stored procedure.
PREPARE stmt FROM "{ call proc1(?,?,?) }"
MAIN DEFINE n INTEGER DEFINE d DECIMAL(6,2) DEFINE c VARCHAR(200) DATABASE test1 EXECUTE IMMEDIATE "create procedure proc1" || " @v1 integer," || " @v2 decimal(6,2) output," || " @v3 varchar(20) output" || " as begin" || " set @v2 = @v1 + 0.23" || " set @v3 = 'Value = ' || cast(@v1 as varchar)" || "end" PREPARE stmt FROM "{ call proc1(?,?,?) }" LET n = 111 EXECUTE stmt USING n IN, d OUT, c OUT DISPLAY d DISPLAY c END MAIN