In SQL, prepared statements are used to templatize similar queries or updates. Adobe Experience Platform Query Service supports prepared statements by using a parameterized query. This can optimize performance, as you no longer need to repetitiously re-parse a query.
When using prepared statements, the following syntaxes are supported:
This SQL query saves the written SELECT query with the name given as PLAN_NAME
. You can use variables, such as $1
in lieu of actual values. This prepared statement will be saved during the current session. Please note that plan names are not case sensitive.
PREPARE {PLAN_NAME} AS {SELECT_QUERY}
PREPARE test AS SELECT * FROM table WHERE country = $1 AND city = $2;
This SQL query uses the prepared statement which was created earlier.
EXECUTE {PLAN_NAME}('{PARAMETERS}')
EXECUTE test('canada', 'vancouver');
This SQL query is used to delete the named prepared statement.
DEALLOCATE {PLAN_NAME}
DEALLOCATE test;
Initially, you can have an SQL query, such as the one below:
SELECT * FROM table WHERE id >= 10000 AND id <= 10005;
The SQL query above will return the following response:
id | firstname | lastname | birthdate | city | country | |
---|---|---|---|---|---|---|
10000 | alexander | davis | 1993-09-15 | example@example.com | Vancouver | Canada |
10001 | antoine | dubois | 1967-03-14 | example2@example.com | Paris | France |
10002 | kyoko | sakura | 1999-11-26 | example3@example.com | Tokyo | Japan |
10003 | linus | pettersson | 1982-06-03 | example4@example.com | Stockholm | Sweden |
10004 | aasir | waithaka | 1976-12-17 | example5@example.com | Nairobi | Kenya |
10005 | fernando | rios | 2002-07-30 | example6@example.com | Santiago | Chile |
This SQL query can be parameterized by using the following prepared statement:
PREPARE getIdRange AS SELECT * FROM table WHERE id >= $1 AND id <= $2;
Now, the prepared statement can be executed by using the following call:
EXECUTE getIdRange(10000, 10005);
When this is called, you will see the exact same results as before:
id | firstname | lastname | birthdate | city | country | |
---|---|---|---|---|---|---|
10000 | alexander | davis | 1993-09-15 | example@example.com | Vancouver | Canada |
10001 | antoine | dubois | 1967-03-14 | example2@example.com | Paris | France |
10002 | kyoko | sakura | 1999-11-26 | example3@example.com | Tokyo | Japan |
10003 | linus | pettersson | 1982-06-03 | example4@example.com | Stockholm | Sweden |
10004 | aasir | waithaka | 1976-12-17 | example5@example.com | Nairobi | Kenya |
10005 | fernando | rios | 2002-07-30 | example6@example.com | Santiago | Chile |
After you have finished using the prepared statement, you can deallocate it by using the following call:
DEALLOCATE getIdRange;