Calling Stored Procedures

Use conn:execute{} to call stored procedures which exist in your database.

The only thing you need to be careful of is to make sure that values coming from external sources are properly escaped using conn:quote{}. Other than this, all you have to do is concatenate your strings to create the stored procedure call - take a look at the strategies for writing dynamic SQL statements.

Take the following example stored procedure:

CREATE PROCEDURE GetPatientByLastName(IN LastName TEXT)
BEGIN
    SELECT * FROM Patients WHERE LastName = LastName;
END;

In Iguana, we can execute this stored procedure using conn:execute{}:

-- Prepare the SQL call 
local sqlCall = "CALL GetPatientByLastName(" .. conn:quote(lastName) .. ")"

-- Execute the stored procedure
local result = conn:execute{sql=sqlCall, live=true}

The SQL statement syntax will vary depending on the database you are working with. For example, MySQL would use CALL where as Microsoft SQL Server would use EXEC to execute a stored procedure. It’s important to know the proper syntax for the database you are working with!