# SQL

## Overview

Zuar Runner can automate SQL statements to any supported database.

.. image:: assets/sql-1.png
	:alt: SQL Connector

Use the Zuar Runner SQL plugin to create SQL jobs containing these SQL
statements.

Typical use cases for SQL jobs include creating schemas, tables,
views, and stored procedures.

## Create a SQL Job

Click **+Add Job.** and choose "SQL job".

.. image:: assets/sql__image-14.png
	:alt: Add Job Image

Fill in the form.

.. image:: assets/sql__image-16.png
	:alt: SQL UI Wizard

- **Title** - title of the job - Best practice naming convention `[SQL] Table or Brief Desc.`
- **Sql** - the SQL query to be sent to the database

To send queries to a different database than Zuar Runner's internal PostgreSQL database, uncheck **"Use default database URI"**

.. image:: assets/sql__image-17.png
	:alt: Adjust DB

Click **"Done"**.

## SQL Job Configuration Options

See the full list of supported [SQL job configuration
options](https://www.zuar.com/api/mitto/schemas/builtin/sql/jobs/job_sql/index.html).

## SQL Syntax and Validation

SQL queries in SQL jobs need to be written in the syntax of the
database they are being sent to.

Zuar Runner does not validate SQL queries when creating a SQL job. Best
practice is to write your SQL queries in another tool (e.g. DataGrip)
to test the SQL, and then when you are ready to save and automate the
SQL, copy the SQL into a Zuar Runner job.

## Example SQL Job

This SQL drops and recreates the `normal_sql` table in the `test`
schema. This is one method for creating new tables in the database.

.. image:: assets/sql__image-19.png
	:alt: SQL Editor

## Commenting in SQL

Zuar Runner only accepts multi-line comments.

Don't use this comment syntax:

.. image:: assets/sql__mitto_sql_enter_err_626x511.png
	:alt: Incorrect comment syntax

Use this comment syntax:

.. image:: assets/sql__mitto_sql_enter_statement_ex1_614x503.png
	:alt: Correct Comment Syntax

## Parameterized SQL

Parameters can be passed to SQL in Zuar Runner jobs. This is useful when
users need to make small changes to SQL jobs frequently (e.g. changing
a date range in a WHERE clause).

Rather than the user having to comb through tons of SQL, they can
simply change a parameter in the SQL jobs JSON config.

Below is an example of using a parameter (`:number`) in a SQL job.

.. image:: assets/sql__image-22.png
	:alt: Using Parameter Example

Instead of hardcoding the value in the SQL itself, the value is defined in the job's config.

.. image:: assets/sql__image-21.png
	:alt: Defining SQL Parameter

Key/value pairs can be added to the `parameters` of the JSON config.