# sqlWrite

See SQL Statements for how to create statements.

### Demo

{% embed url="<https://youtu.be/vs3uR5BRPKU>" %}

### Syntax

sqlWrite(statementName, parameterJson, cmdOptions)

### Parameters

| Parameter                | Type   | Description                                                                                                              |
| ------------------------ | ------ | ------------------------------------------------------------------------------------------------------------------------ |
| statementName            | string | The name of the SQL statement to execute                                                                                 |
| parameterJson            | object | A JSON object with additional parameters. All controls in the current configuration are automatically set as parameters. |
| cmdOptions               | object | (optional) A JSON object with options for the request                                                                    |
| cmdOptions.timeout       | number | (optional) Timeout for the SQL request in milliseconds. Default is 30 seconds.                                           |
| cmdOptions.connectionKey | string | (optional) A key to a custom settings entry with the connection string to an external MSSQL database                     |

### Return Value

The method returns a Promise that resolves to an object with the following properties:

| Property            | Type   | Description                                                                                 |
| ------------------- | ------ | ------------------------------------------------------------------------------------------- |
| ModifiedRecordCount | number | The number of affected rows for INSERT, UPDATE or DELETE operations                         |
| SubData             | array  | Returned data when the SQL statement includes SELECT, OUTPUT INSERTED, or RETURNING clauses |

### Description

The sqlWrite method executes an SQL statement that is stored in the database. This method is intended for operations that modify data in the database, but it can also return data if the SQL statement is appropriately structured.

The method automatically detects whether the SQL statement should return results:

* For simple INSERT, UPDATE, or DELETE statements, only the count of affected records is returned.
* If the SQL statement begins with SELECT or includes OUTPUT INSERTED or RETURNING clauses, the returned data is made available in the SubData array.

### Examples

#### Simple INSERT Statement without Return Value

```javascript
// Execute a simple INSERT statement
const result = app.sqlWrite("InsertNewCustomer", {
    customerName: "Example Company Ltd", 
    contactPerson: "Jane Doe"
});
console.log(`${result.ModifiedRecordCount} records were inserted`);

```

#### UPDATE Statement with Custom Options

```javascript
// UPDATE with longer timeout
const result = app.sqlWrite("UpdateCustomerAddress", {
    customerId: 12345,
    street: "123 Main Street",
    city: "London",
    zipCode: "E1 6AN"
}, {
    timeout: 60000 // 60 seconds timeout
});
console.log(`${result.ModifiedRecordCount} customer addresses were updated`);
```

#### Statement with Data Return (OUTPUT INSERTED)

```javascript
// INSERT with OUTPUT INSERTED clause
const result = app.sqlWrite("InsertProductWithReturn", {
    productName: "New Product",
    price: 99.99,
    category: "Electronics"
});

if (result.SubData && result.SubData.length > 0) {
    console.log(`Product with ID ${result.SubData[0].ProductId} was created`);
    // Access other returned fields
    console.log(`Creation date: ${result.SubData[0].CreatedDate}`);
}
```

#### Using an External Database Connection

```javascript
// Execute SQL statement on external database
const result = app.sqlWrite("DeleteExpiredOrders", {
    olderThanDays: 365
}, {
    connectionKey: "ExternalArchiveDB"
});
console.log(`${result.ModifiedRecordCount} old orders were deleted`);
```

### Notes

* The SQL statements must be predefined in the system.
* Use [sqlRead](/brixxbox-documentation/client-api-reference/functions/sqlread.md) for pure read operations when you don't need to modify data.
* All parameters are automatically protected against SQL injection attacks.
* When using OUTPUT INSERTED or RETURNING clauses, the results are returned in the SubData array while the number of inserted/updated rows is stored in ModifiedRecordCount.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://brixxbox.gitbook.io/brixxbox-documentation/client-api-reference/functions/sqlwrite.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.