Static Parameterized SQL
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
Within stored procedures, you can write queries that contain PSQL variables representing parameters.
In the following example, SELECT id, description FROM product ORDER BY id LIMIT row_
is a static parameterized query.row_
is an PSQL variable representing a parameter.
DROP DATABASE IF EXISTS singlestore_docs_example;CREATE DATABASE singlestore_docs_example;use singlestore_docs_example;CREATE TABLE product(id INT, description TEXT);INSERT INTO product VALUES (1, 'white paper'), (2, 'blue pen'), (3, 'black pen');DELIMITER //CREATE PROCEDURE get_product_data(row_count INT) RETURNS QUERY(id INT, description TEXT) ASDECLAREqry QUERY(id INT, description TEXT) = SELECT id, descriptionFROM product ORDER BY id LIMIT row_count;BEGINRETURN qry;END//DELIMITER ;ECHO get_product_data(2);
Output:
+------+-------------+
| id | description |
+------+-------------+
| 1 | white paper |
| 2 | blue pen |
+------+-------------+
Within stored procedures, you define static queries using the QUERY data type.
Parameters in static queries always represent constants.row_
evaluates to the constant value 2
.
Other examples of static parameterized queries are shown below.
SELECT * FROM order WHERE amount >= <parameter>
INSERT INTO item (name) VALUES (<parameter>)
Last modified: June 5, 2023