Worked Examples

This topic provides examples that show you how to use SQL Prompt.

You can follow the examples on your own system. The examples in this topic use SQL Server 2005 and you will need access to a SQL Server on which the AdventureWorks example database has been installed.

The following examples are provided:

Typing a simple query

Before you try this example, ensure the Qualify object names option is selected. To select this option, on the SQL Prompt menu, click Options and then click the Style tab.

Type SELECT * FROM and press SPACEBAR.
Press CTRL+SPACEBAR to display the candidate list.
The candidate list displays the Suggested candidates category; tables are displayed at the top of the list.
Type c to filter the list by table names that begin with c.
The Contact table is highlighted.
Press ENTER to insert the Contact table.
SQL Prompt inserts the table name, qualifies it with the schema name Person, and surrounds the identifiers with brackets [ ].
Type WHERE and press SPACEBAR.
Press CTRL+SPACEBAR to display the candidate list.
The candidate list displays column names for the Contact table and associated data types.
Type L and press ENTER to insert the LastName column.
Type LIKE 'Fr%' so that your query returns rows where LastName begins with Fr.
By default, when you type an opening quotation mark, SQL Prompt automatically inserts the closing quotation mark.
Delete the * and press CTRL+SPACEBAR to display the candidate list.
The candidate list displays columns from the Contact table.
Type f and press ENTER to insert the FirstName column.
Press COMMA, type L, and then press ENTER to insert the LastName column.

Expanding column lists

When you type SELECT * and you specify the tables or views in the FROM clause, you can complete the column list by placing the insertion point after the * and pressing TAB.

For example:

Type:
SELECT * from Contact
Place the insertion point after the *
Press TAB.
SQL Prompt expands the column list.

SQL Prompt also completes the column list for you when you type a SELECT table.* fragment.

For example:

Type:
SELECT Contact.*
Press TAB.
SQL Prompt expands the column list, qualifying the columns with the table name and schema (or owner) name.

Executing stored procedures

SQL Prompt inserts the parameters for stored procedures when you type EXEC and you select a stored procedure. Before you try this example, ensure the Insert parameters for functions and stored procedures and Insert hints for data types options are selected; to select these options, on the SQL Prompt menu, click Options and then click the Auto Insert tab.

In your query editor, type EXEC and then press SPACEBAR
Press CTRL+SPACEBAR to display the candidate list.
SQL Prompt displays a list of stored procedures at the top of the candidate list.

The schema panel shows the creation SQL for the highlighted stored procedure.

For example:
Select a stored procedure by pressing UP ARROW or DOWN ARROW to highlight it, and then press ENTER to insert it.
SQL Prompt inserts the parameters for the stored procedure and inserts the associated data types as comments.

For example:

Using snippets

SQL Prompt is pre-configured with default snippets. To see the snippets, press CTRL+SPACEBAR and then CTRL+LEFT ARROW to display the Snippets category.

This example shows you how to insert the snippet code for disabling all triggers on a table.

Type:
atdta
Press CTRL+SPACEBAR to display the candidate list.
The candidate list displays a description of the snippet and the schema panel displays the snippet code.
Press ENTER to insert the snippet code.
SQL Prompt places the insertion point after TABLE for you to type the table name.