Static Parameterized SQL

Within stored procedures, you can write queries that contain MPSQL variables representing parameters. These queries are known as static parameterized queries.

In the following example, SELECT id, description FROM product ORDER BY id LIMIT row_count; is a static parameterized query. row_count is an MPSQL 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) AS
DECLARE
  qry QUERY(id INT, description TEXT) = SELECT id, description
    FROM product ORDER BY id LIMIT row_count;
BEGIN
  RETURN 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. In the above example, the variable row_count 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>)