Toad for SQL Server 7.4.1 - Installation Guide

Automation and Variables

Summary

This tutorial provides several examples of how to create and use variables in Automation scripts.

In this tutorial you will learn:

• How to use the Set Variable activity in Automation
• How to use a variable with the Select to File activity in Automation
• How to use a variable with the Send Email activity in Automation
• How to add a comment to the Automation Log

You will need:

• A sample database
• A query or query file

Introduction

Automation variables are a powerful tool that can greatly extend your scripts. Variables can add flexibility and customization. Variables can be used in almost every Automation activity. But what is a variable? A variable is a symbolic name that has a value assigned to it, a value that may change.

Variables play an important role in scripts. Variables can be used to represent and store data that may be different each time the script executes. For example, if you include a bind variable in a SQL statement, Toad prompts you for the value of the variable each time you execute the statement. In this way, you can change the variable's value with each execution, which returns a different result set, without changing the SQL statement.

Follow along with this tutorial to learn how variables can be used in Automation. The query examples given in this tutorial are intended to be used with the Toad Sample Database (available with Toad Data Point). If you are using a different sample database, use similar queries written for your database.

Create Script

1. To get started, connect to your sample database.
2. Select Tools | Automation to open a new Automation window.
3. Click the Set Variable activity in the Toolbox to add it to the Automation design window.
4. In the Variable name field, enter a name for your variable. For this example, enter Day.
5. In the Variable type field, select SQL. The variable value options expand to allow you to specify a SQL statement to use to set the value of your variable.
6. In the Variable value field, select a database connection. If you are following along using the Toad Sample Database, do the following:
   1. Select a connection from the connection list.

   2. Enter the SQL statement that returns the current day of the week for your DBMS similar to the following:

      Select Format(DatePart('w', Date()),'dddd')

      

7. Now, to verify, let's display the value of the variable in the Log.
   1. Click the Log Comment activity in the Toolbox to add it to the Automation workflow (add it just after the Set Variable activity).

   2. In the Activity Input tab, enter the following in the Log Message text box:

        Value = #Day#

      Note: In most cases, surround the variable name with hash symbols (#) when you want to express the value of the variable. In SQL statements, you can use bind variable syntax.

8. Click to save and run your script.

9. After the script executes, review the Log. The Log comment you added displays the value of the variable.

   

Use a Variable in a Report File Name

You can set a variable value and then express that variable value in a file name.

1. In the design window, right-click the Log Comment activity and select Delete to remove it from the Automation workflow.
2. Click the Select to File activity to add it to the Automation workflow just after the Set Variable activity. You will use this activity to create a report.
3. In the Activity Input tab, select your sample database from the connection list.
4. Enter a SQL query or select a SQL file. (If you are entering a SQL query, you must first deselect Link to SQL file.)
5. Click the Excel button in the Type field in the Export File section.

6. Click in the Name field. Browse to a location to save your report, and enter the following file name:

           #Day#_Report.xlsx

7. Click to save and run your script.

8. After the script executes, review the Log. Notice that the script created an Excel file that includes the current day of the week in the file name. If you execute the script tomorrow, the file will include tomorrow's day of the week in the file name.

   

Use a Variable to Filter Query Results

You can set a variable and then use that variable to filter query results.

1. In the design window, click the Select to File activity in your workflow to select it.

2. To demonstrate this example, modify your query to filter your results using the value of your variable. For example, enter a query similar to the following:

     Select * from Contact

     Where Format(DatePart('w', Birth_Date),'dddd') = :Day

   This query selects the contacts whose birthdays occurred on the same day of the week as the current variable value. The variable is indicated in the SQL statement by using bind variable syntax, in this case a colon (:) preceding the variable name. When Toad executes the statement, it binds the value of the variable (in this case, Monday) into the SQL statement .

3. Click to save and run your script.

4. After the script executes, click the hyperlink to view the output file. If you used the query statement provided, notice that the result set includes only contacts with Birth_Date dates that fall on the same week day as the value currently assigned to your variable (today's day of the week).

Use a Variable in an Email

You can set a variable and then use that variable within an email. For example, use the variable in the subject line or body.

1. Using the same script, click the Send Email activity to add it to the Automation workflow just after the Select to File activity.
2. For this tutorial, enter your email address in the To and the From fields.
3. In the Subject field of the email, enter #Day#_Report.
4. In the Body field, enter #Day#_Report.
5. Click Add Attachments. The File Collection Editor displays the list of files that are generated by your script. Select #Day#_Report.xlsx and click Add to add it as an attachment to the email.
6. Click to save and run your script. The script generates an Excel report with a dynamic file name (includes the day of the week) and then sends the file in an email with a dynamic subject line and message.

Automation variables can make your scripts and outputs extensible and dynamic.

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.

Related Topics

Getting Started with Automation

Using Variables in Automation