About Flow Control for Scripts

You can use Toad Scripts to improve flow control in your SQL scripts. Toad Scripts support script functions and statement functions.

Caution: Toad Script functions automatically terminate a batch. To avoid terminating before a batch completes, make sure that any Toad Script functions are placed between batches.

Script functions are formatted as comments using "--TOAD:" as shown in the following example:

In this example, the function sets the predefined parameter "a" to the value 10. "SET" is the actual function and "a = 10" are the parameters to the SET function.

Click here to see supported script functions.

Statement functions are used inside of statements and unlike script functions, they cannot be used in products outside of Toad. (Because script functions are comments they are ignored by other products). The following displays an example of a statement function:

In this example, the statement function is in bold inserts the value of the bind variable as a literal value before the statement is sent to the server. This is usefule when you need to use a bind variable in places where they are not allowed.

Syntax	Description	Example
ASK {parameter} = {expression}	Displays a dialog containing the text of the expression with a yes and no button. If you click the Yes button, the parameter is assigned a true value. If you press No, a false value is assigned, which can be used with either a WHILE or an IF function.	-- TOAD: ASK a = 'Would you like to continue?'
CHDIR {expression}	Changes the current working directory of the current execution. By default, the working directory when executing a script is the location of the script. The path can be either absolute or relative to the current working directory of the script execution.	-- TOAD: CHDIR 'c:\'
COMMIT	Commits all database connections for the current Toad connection. See Understand Toad Connections for more information.	-- TOAD: COMMIT
GROUPBY {column}[,{column}]	Instructs that columns in the script result of the statement following this call are initially grouped in the grid. The columns can be quoted with either [], '', "" or `` characters or without quotes as long as the column names do not include whitespace or , characters.	-- TOAD: GROUPBY [Col1],'Col2',Col3
IF {expression} ELSE ENDIF	If the expression specified evaluates to true, everything between IF and the corresponding ENDIF or ELSE function is executed. If the ELSE function is specified, everything after that function is executed if the expression evaluates to false.	-- 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
INCLUDE {expression}	Includes another file that is executed in the current script.	-- TOAD: INCLUDE 'otherscript.sql'
NOTIFY {expression}	Displays a notification in the lower right corner of the desktop screen with the message specified by the expression.	-- TOAD: NOTIFY 'Pekaboo!'
PRINT {expression}	Temporarily displays a message specified by the expression in the status bar of the Toad window.	-- TOAD: PRINT 'This is probably not that important...'
PROMPT {parameter} = {expression}	Displays a dialog containing the text of the expression and prompts for a value. The value entered is assigned to the parameter.	-- TOAD: PROMPT a = 'Give me a value!'
ROLLBACK	Rolls back all database connections for the current Toad connection. See Understand Toad Connections for more information.	-- TOAD: ROLLBACK
SET {parameter} = {expression}	Sets the value of a predefined parameter to the value of an expression.	-- TOAD: SET a = (10 + 20) / 10 * 5
SLEEP {expression}	Pauses for the number of seconds set in the expression between statements.	-- TOAD: SLEEP 10
UNSET {variable}	Removes the value of a predefined variable.	-- TOAD: UNSET a
WHILE {expression} ENDWHILE	Repeats the expression until the ENDWHILE statement is true.	--TOAD: SET a = 1 -- TOAD: TOAD: WHILE a < 10 -- TOAD: PRINT 'We are counting up. Now we are at ' \|\| a -- TOAD: SET a = a + 1 -- TOAD: ENDWHILE

Click here to see supported statement functions.

Example

Syntax	Description	Example
{{Format({Format String} ...)}}	Formats a string using the .Net String.Format method format and returns the result. See http://msdn.microsoft.com/en-us/library/system.string.format.aspx for more information about the .NET String.Format Method.	SELECT {{Format('Hello {0}', 'World')}}
{{Quote({literal})}}	Takes the passed in value and inserts the value as a quoted SQL literal.	-- TOAD: SET database = 'AdventureWorks' -- TOAD: SET owner = 'Person' -- TOAD: SET table = 'Address' select * from {{Quote(:database)}}. {{Quote(:owner)}}. {{Quote(:table)}}
{{Round({number}[, {decimals}])}}	Takes a numerical value and returns it rounded to either an integer or to a specific number of decimals.	-- TOAD: SET meaning = 41.52 select {{Round(:meaning)}}, {{Round(:meaning, 1)}}
{{StringQuote({literal}[, {decimals}])}}	Takes the passed in value and inserts the value as a quoted string.	-- TOAD: SET helloworld = 'Hello World' select {{StringQuote(:helloworld)}}
{{Unquote({literal})}}	Takes the passed in value and inserts the value into the script as a string.	-- TOAD: SET from = 'FROM' select * {{Unquote(:from)}} AdventureWorks.Person.Address

This example creates 10 tables called toadscripttest1 to toadscripttest10. The first line sets a predefined bind variable a to 1. The second line checks whether the value of a is less than 10, and as long as it is, it repeats everything between this row and the corresponding ENDWHILE line. 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.