Static Parameterized SQL

Within stored procedures, you can write queries that contain PSQL 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 PSQL variable representing a parameter.

SQL

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.

SQL

SELECT * FROM order WHERE amount >= <parameter>

SQL

INSERT INTO item (name) VALUES (<parameter>)

Static Parameterized SQL

Was this article helpful?

Was this article helpful?