# Use prepared statements
Use prepared statements

You can use a prepared statement for repeated execution of the same query with different query parameters. A prepared statement contains parameter placeholders whose values are supplied at execution time.

**Note**  
The maximum number of prepared statements in a workgroup is 1000.

**Topics**
+ [SQL syntax](querying-with-prepared-statements-sql-statements.md)
+ [Use the Athena console](querying-with-prepared-statements-executing-prepared-statements-without-the-using-clause-athena-console.md)
+ [Use the AWS CLI](querying-with-prepared-statements-cli-section.md)

# SQL syntax for prepared statements
SQL syntax

You can use the `PREPARE`, `EXECUTE` and `DEALLOCATE PREPARE` SQL statements to run parameterized queries in the Athena console query editor. 

 
+ To specify parameters where you would normally use literal values, use question marks in the `PREPARE` statement.
+ To replace the parameters with values when you run the query, use the `USING` clause in the `EXECUTE` statement.
+ To remove a prepared statement from the prepared statements in a workgroup, use the `DEALLOCATE PREPARE` statement.

The following sections provide additional detail about each of these statements.

**Topics**
+ [

# PREPARE
](querying-with-prepared-statements-prepare.md)
+ [

# EXECUTE
](querying-with-prepared-statements-execute.md)
+ [

# DEALLOCATE PREPARE
](querying-with-prepared-statements-deallocate-prepare.md)

# PREPARE


Prepares a statement to be run at a later time. Prepared statements are saved in the current workgroup with the name that you specify. The statement can include parameters in place of literals to be replaced when the query is run. Parameters to be replaced by values are denoted by question marks.

## Syntax


```
PREPARE statement_name FROM statement
```

The following table describes these parameters.


****  

| Parameter | Description | 
| --- | --- | 
| statement\$1name | The name of the statement to be prepared. The name must be unique within the workgroup. | 
| statement | A SELECT, CTAS, or INSERT INTO query. | 

## PREPARE examples


The following examples show the use of the `PREPARE` statement. Question marks denote the values to be supplied by the `EXECUTE` statement when the query is run.

```
PREPARE my_select1 FROM
SELECT * FROM nation
```

```
PREPARE my_select2 FROM
SELECT * FROM "my_database"."my_table" WHERE year = ?
```

```
PREPARE my_select3 FROM
SELECT order FROM orders WHERE productid = ? and quantity < ?
```

```
PREPARE my_insert FROM
INSERT INTO cities_usa (city, state)
SELECT city, state
FROM cities_world
WHERE country = ?
```

```
PREPARE my_unload FROM
UNLOAD (SELECT * FROM table1 WHERE productid < ?)
TO 's3://amzn-s3-demo-bucket/'
WITH (format='PARQUET')
```

# EXECUTE


Runs a prepared statement. Values for parameters are specified in the `USING` clause.

## Syntax


```
EXECUTE statement_name [USING value1 [ ,value2, ... ] ]
```

*statement\$1name* is the name of the prepared statement. *value1* and *value2* are the values to be specified for the parameters in the statement.

## EXECUTE examples


The following example runs the `my_select1` prepared statement, which contains no parameters.

```
EXECUTE my_select1
```

The following example runs the `my_select2` prepared statement, which contains a single parameter.

```
EXECUTE my_select2 USING 2012
```

The following example runs the `my_select3` prepared statement, which has two parameters.

```
EXECUTE my_select3 USING 346078, 12
```

The following example supplies a string value for a parameter in the prepared statement `my_insert`.

```
EXECUTE my_insert USING 'usa'
```

The following example supplies a numerical value for the `productid` parameter in the prepared statement `my_unload`.

```
EXECUTE my_unload USING 12
```

# DEALLOCATE PREPARE


Removes the prepared statement with the specified name from the list of prepared statements in the current workgroup.

## Syntax


```
DEALLOCATE PREPARE statement_name
```

*statement\$1name* is the name of the prepared statement to be removed.

## Example


The following example removes the `my_select1` prepared statement from the current workgroup.

```
DEALLOCATE PREPARE my_select1
```

# Run interactive prepared statements in the Athena console
Use the Athena console

If you run an existing prepared statement with the syntax `EXECUTE` *prepared\$1statement* in the query editor, Athena opens the **Enter parameters** dialog box so that you can enter the values that would normally go in the `USING` clause of the `EXECUTE ... USING` statement.

**To run a prepared statement using the **Enter parameters** dialog box**

1. In the query editor, instead of using the syntax `EXECUTE prepared_statement USING` *value1*`,` *value2* ` ...`, use the syntax `EXECUTE` *prepared\$1statement*.

1. Choose **Run**. The **Enter parameters** dialog box appears.  
![\[Entering parameter values for a prepared statement in the Athena console.\]](http://docs.aws.amazon.com/athena/latest/ug/images/querying-with-prepared-statements-2.png)

1. Enter the values in order in the **Execution parameters** dialog box. Because the original text of the query is not visible, you must remember the meaning of each positional parameter or have the prepared statement available for reference.

1. Choose **Run**.

# Use the AWS CLI to create, execute, and list prepared statements
Use the AWS CLI

You can use the AWS CLI to create, execute, and list prepared statements.

**Topics**
+ [Create](querying-with-prepared-statements-creating-prepared-statements-using-the-aws-cli.md)
+ [Execute](querying-with-prepared-statements-cli-executing-prepared-statements.md)
+ [List](querying-with-prepared-statements-listing.md)

# Create prepared statements using the AWS CLI
Create

To use the AWS CLI to create a prepared statement, you can use one of the following `athena` commands:
+ Use the `create-prepared-statement` command and provide a query statement that has execution parameters.
+ Use the `start-query-execution` command and provide a query string that uses the `PREPARE` syntax.

## Use create-prepared-statement


In a `create-prepared-statement` command, define the query text in the `query-statement` argument, as in the following example.

```
aws athena create-prepared-statement 
--statement-name PreparedStatement1 
--query-statement "SELECT * FROM table WHERE x = ?" 
--work-group athena-engine-v2
```

## Use start-query-execution and the PREPARE syntax


Use the `start-query-execution` command. Put the `PREPARE` statement in the `query-string` argument, as in the following example:

```
aws athena start-query-execution 
--query-string "PREPARE PreparedStatement1 FROM SELECT * FROM table WHERE x = ?" 
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://amzn-s3-demo-bucket/..."}'
```

# Execute prepared statements using the AWS CLI
Execute

To execute a prepared statement with the AWS CLI, you can supply values for the parameters by using one of the following methods:
+ Use the `execution-parameters` argument.
+ Use the `EXECUTE ... USING` SQL syntax in the `query-string` argument.

## Use the execution-parameters argument


In this approach, you use the `start-query-execution` command and provide the name of an existing prepared statement in the `query-string` argument. Then, in the `execution-parameters` argument, you provide the values for the execution parameters. The following example shows this method.

```
aws athena start-query-execution 
--query-string "Execute PreparedStatement1" 
--query-execution-context "Database"="default" 
--result-configuration "OutputLocation"="s3://amzn-s3-demo-bucket/..."
--execution-parameters "1" "2"
```

## Use the EXECUTE ... USING SQL syntax


To run an existing prepared statement using the `EXECUTE ... USING` syntax, you use the `start-query-execution` command and place the both the name of the prepared statement and the parameter values in the `query-string` argument, as in the following example:

```
aws athena start-query-execution 
--query-string "EXECUTE PreparedStatement1 USING 1"
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://amzn-s3-demo-bucket/..."}'
```

# List prepared statements using the AWS CLI
List

To list the prepared statements for a specific workgroup, you can use the Athena [list-prepared-statements](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/athena/list-prepared-statements.html) AWS CLI command or the [ListPreparedStatements](https://docs.aws.amazon.com/athena/latest/APIReference/API_ListPreparedStatements.html) Athena API action. The `--work-group` parameter is required.

```
aws athena list-prepared-statements --work-group primary
```