Open topic with navigation

Learn more about Toad for SQL Server
at Dell Software

Find solutions and downloads at the
Toad for SQL Server Support Portal

Toad for SQL Server 6.6
You are here: Edit SQL > Create and Edit SQL > About Toad Script

About Toad Script

Toad Script is a set of predefined functions within Toad that can be used in SQL scripts that you run in Toad. These functions are designed to enhance the process of running a script in Toad. You can use these built-in functions to instruct Toad to perform actions at a particular point in script execution, such as prompting you for a value. You can also use Toad Script inside statements, for example to format strings, change stings to literals, and change literals to strings.

Toad Script is composed of two types of functions: script functions and statement functions.

The following are some examples of what Toad Script can do.

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.

Before Using Toad Script

Please note the following before using Toad Script.

A Toad Script Example

Here is a basic example of how Toad Script functions can 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 creates 10 tables named toadscripttest1 through toadscripttest10. The first line sets a predefined variable a to 1. The second line determines if the value of a is less than 10. If it is, the script proceeds to the third line, which executes a CREATE TABLE statement and appends the table name with the value of :a unquoted. The fourth line increments a by 1. The last line ends the While statement.

For a detailed example of how to use Toad Script functions, see Toad Script Example.

Script Functions

In Toad Script, script functions are formatted as comments by prefixing the function with "--TOAD:" as shown in the following example. When adding a script function to your script, always prefix it with --TOAD:.

-- TOAD: SET a = 10

This example sets the predefined parameter a to the value 10.

The following table lists and describes the script functions of Toad Script.

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 the value of true. If you click No, the value of false is assigned.

This function can be used with either the WHILE or the 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 Performs a commit on all database connections for the current Toad connection. For more information about Toad connections, see Understand Toad Connections.

-- TOAD: COMMIT
GROUPBY {column}[,{column}] Instructs Toad how to initially group columns in the result set grid for the statement that follows this call. The columns can be quoted with either [], '', "" or `` characters. Quotes are not necessary if the column names do not include blank spaces or commas (,).

-- 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, then 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 into the execution of this script. Specify the file name in {expression}.

-- 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 'Hello!'
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

Performs a rollback on all database connections for the current Toad connection. For more information about Toad connections, see Understand Toad Connections.

-- 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

Statement Functions

In Toad Script, statement functions are used inside of statements. The following is an example of a statement function.

SELECT {{Unquote(:a)}}

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

The following table lists and describes the statement functions of Toad Script.

Syntax Description Example

{{Format({Format String} ...)}}

Formats a string using the .Net String.Format method 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({identifier})}}

Inserts the value as a quoted (if needed) SQL identifier. If the identifier requires quotes (for example, if the identifier contains blank spaces), quotes are added. If the identifier does not require quotes, they are not added.

Use this function for identifiers, such as a database name or a table name.

See Use Quote Function to Substitute a String at Run Time to learn how to use the Quote function.

-- 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 an integer or rounded to the specified number of decimals.

-- TOAD: SET meaning = 41.52

select {{Round(:meaning)}}

select {{Round(:meaning, 1)}}
{{StringQuote({literal})}} Inserts the value as a quoted string value.

-- TOAD: SET helloworld = 'Hello World'

select {{StringQuote(:helloworld)}}
{{Unquote({literal})}}

Inserts the value into the script as a string.

Use this function for literals such as keywords.

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

 

   

 

of