Flow control for your scripts

A feature that has actually been in Toad for a long time but has still been more or less undocumented and I assume since I see no questions about it that nobody is using is a feature we internally refer to as Toad Script.

Note that the examples in this are written to work for SQL Server or MySQL but should work with minimal changes on DB2 as well (The Toad Script part is identical).

Toad script is comprised of two different parts. Script functions and statement functions. A script function looks like a comment and looks something like this.

-- TOAD: SET a = 10

In this case the "-- TOAD:" part indicates that this is a toad script function and all Toad script functions look like this. "SET" is the actual function and "a = 10" are the parameters to the set function. In this case this function sets the predefined parameter "a" to the value 10.

Statement functions are used inside of statements and are unlike script functions not portable to other products than Toad (Since script functions are comments they are just ignored by other products). Here is an example of a statement function.

SELECT {{Unquote(:a)}}

In the example the statement function is in bold and will insert the value of the bind parameter as a literal value before the statement is sent to the server (In the case you want to use a bind variable in places where they are not allowed this is useful).

Before we delve deeper into details lets look at a more useful example to how this could be used.

-- TOAD: SET a = 1
-- TOAD: WHILE a < 10
CREATE TABLE toadscripttest{{Unquote(:a)}} (id INT);
-- TOAD: SET a = a + 1
-- TOAD: ENDWHILE

This example when run will create 10 tables called toadscripttest1 to toadscripttest10. To step through it. The first line sets a predefined bind variable a to 1. The second line checks if the value of a is less than 10 and as long as it is it will repeat everything between this row and the corresponding ENDWHILE line a few lines below. The third line executes a create table statement and inserts the predefined value of :a unquoted. The fourth line increments a with 1 and the last line ends the while statement.

Script functions

ASK

Syntax: ASK {parameter} = {expression}

Example: -- TOAD: ASK a = 'Would you like to continue?'

This method displays a dialog containing the text of the expression with a yes and no button. If the yes button the parameter will be assigned a true value and if the no button is pressed a will be assigned a false value which can be used with either a WHILE or an IF function.

CHDIR

Syntax: CHDIR {expression}

Example: -- TOAD: CHDIR 'c:\'

This function changes the current working directory of the current execution. By default the working directory when executing a script is the location where the script is located. The path can be either absolute or relative to the current working directory of the script execution.

COMMIT

Syntax: COMMIT

Example: -- TOAD: COMMIT

Perform a commit on all database connections for the current toad connection.

GROUPBY

Syntax: GROUPBY {column}[,{column}]

Example: -- TOAD: GROUPBY [Col1],'Col2',Col3

This function instructs that a column in the script result of the statement following this call will be grouped by in the grid initially. The columns can be quoted with either [], '', "" or `` characters or without quotes as long as the column names don't include whitespace or , characters (This method was added in 4.5).

IF - ELSE - ENDIF

Syntax: IF {expression}

Syntax: ELSE

Syntax: ENDIF

Example:
-- TOAD: ASK a = 'Do you agree?'
-- TOAD: IF a
-- TOAD: NOTIFY 'Nice to be in agreement!'
-- TOAD: ELSE
-- TOAD: NOTIFY 'Hopefully you will come around!'
-- TOAD: ENDIF

This function allows for flow control. If the expression specified evaluates to true everything between if and the corresponding endif or else function will be executed. If the else function is specified then everything after that will be executed if the expression evaluates to false.

INCLUDE

Syntax: INCLUDE {expression}

Example: -- TOAD: INCLUDE 'otherscript.sql'

This function allows you to include another file which filename is specified with the expression into the execution of this file.

NOTIFY

Syntax: NOTIFY {expression}

Example: -- TOAD: NOTIFY 'Pekaboo!'

Displays a notification in the lower right corner of your screen with the message specified by the expression.

PRINT

Syntax: PRINT {expression}

Example: -- TOAD: PRINT 'This is probably not that important...'

Displays a message specified by the expression temporarily in the status bar of the Toad window.

PROMPT

Syntax: PROMPT {parameter} = {expression}

Example: -- TOAD: PROMPT a = 'Give me a value!'

This method displays a dialog containing the text of the expression and asks for a value. The value entered will be assigned to the parameter.

ROLLBACK

Syntax: ROLLBACK

Example: -- TOAD: ROLLBACK

Perform a rollback on all database connections for the current toad connection.

SET

Syntax: SET {parameter} = {expression}

Example: -- TOAD: SET a = (10 + 20) / 10 * 5

This method will set the value of a predefined parameter to the value of an expression.

UNSET

Syntax: UNSET {parameter}

Example: -- TOAD: UNSET a

Remove the value of a predefined variable.

WHILE - ENDWHILE

Syntax: WHILE {expression}

Syntax: ENDWHILE

Example:
-- TOAD: SET a = 1
-- TOAD: WHILE a < 10
-- TOAD: PRINT 'We are counting up. Now we are at ' || a
-- TOAD: SET a = a + 1
-- TOAD: ENDWHILE

Statement functions

Format

Syntax: {{Format({Format String} ...)}}

Example: SELECT {{Format('Hello {0}', 'World')}}

This method takes a string and formats it using the .Net String.Format method format and returns the result.

Quote

Syntax: {{Quote({literal})}}

Example:
-- TOAD: SET database = 'AdventureWorks'
-- TOAD: SET owner = 'Person'
-- TOAD: SET table = 'Address'
select * from {{Quote(:database)}}.{{Quote(:owner)}}.{{Quote(:table)}}

This function takes the passed in value and inserts the value as a quoted (if needed) SQL literal.

Round

Syntax: {{Round({number}[, {decimals}])}}

Example:
-- TOAD: SET meaning = 41.52
select {{Round(:meaning)}}, {{Round(:meaning, 1)}}

This function takes a numerical value and return it rounded to either an integer or to a specific number of decimals.

StringQuote

Syntax: {{StringQuote({literal}[, {decimals}])}}

Example:
-- TOAD: SET helloworld = 'Hello World'
select {{StringQuote(:helloworld)}}

This function takes the passed in value and inserts the value as a quoted string.

Unquote

Syntax: {{Unquote({literal})}}

Example:
-- TOAD: SET from = 'FROM'
select * {{Unquote(:from)}} AdventureWorks.Person.Address

This function takes the passed in value and inserts the value right into the script as a string.