Call a redshift stored procedure with dynamic parameter from boto3 lambda

Question

I have a lambda that needs to call a stored proc in redshift with a parameter value not known at runtime. I have narrowed the code down to this in the call:

```
response = rs_client.execute_statement(
            ClusterIdentifier='my_cluster_name',
            Database='my_db_name',
            DbUser='my_user'',
            Sql='call my_schema.this_is_my_proc()',
            Parameters=[{'name': 'in_param', 'value': 'this is the parameter'}],
            StatementName='Test of SP call'
        )
```
The above works fine with the creds (I have anonymised them) and with no parameter and also a hardcoded parameter in the Sql argument i.e. `Sql=call my_schema.this_is_my_proc('this is hardcoded)'` so that's not the issue. I also tried passing in the parameter as a formatted string e.g.

```
param = 'this is the parameter'
stmnt = f'call my_schema.this_is_my_proc({param})',

```
The message I get back from the lambda Test execution is:

`An error occurred (ValidationException) when calling the ExecuteStatement operation: Parameters list contains unused parameters. Unused parameters: [in_param]`

Is it possible to call Redshift stored procs that have parameters from a lambda?

TIA

Accepted Answer

Hi, 
as mentioned in the [documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html) the parameter should be typed in the sql statement in the format :parameter.

following the doc your code should look like:

```

response = rs_client.execute_statement(
            ClusterIdentifier='my_cluster_name',
            Database='my_db_name',
            DbUser='my_user'',
            Sql='call my_schema.this_is_my_proc(:in_param)',
            Parameters=[{'name': 'in_param', 'value': 'this is the parameter'}],
            StatementName='Test of SP call'
        )

```
hope this helps,

Call a redshift stored procedure with dynamic parameter from boto3 lambda

相關內容