Generating SQL Statements
With DatabaseSpy Enterprise Edition, in addition to standard SQL statements, you can also run special kinds of SQL statements that are referred to as "SQL templates".
If you haven't done that already, first open the demo NanonullEnterprise.qprj project, see Opening the Tutorial Project. You will find a demo SQL template called SQLTemplate1.sql in the SQL Templates folder inside the Project Window.
To run the demo SQL template:
1.Double-click the SQLTemplate1.sql file to open it in the SQL Editor.
2.Click Execute 
. A dialog box is now displayed, which prompts you to enter a parameter value.
3.Double-click the row under :addresstype, type "home" as parameter value, and click OK.
Notice that only addresses of type "home" (fifth column) are retrieved from the addresses table.
4.Click Execute and this time enter "work" as parameter value. You will notice that only addresses of type "work" are now retrieved.
The steps above essentially illustrate how a typical SQL template works: it provides the option to make certain parts of the SQL query conditional. The conditional parts are called "parameters" and are marked with a colon character (see the :addresstype parameter inside the SQL statement). When you attempt to run such a query, DatabaseSpy will prompt you to fill in the parameter value (or multiple values, if applicable) as required.
Apart from executing SQL statements conditionally, you can also use SQL templates to generate multiple SQL statements with a click of a button. Let's try the following example:
1.Right-click the addresses table in the Online Browser, and select Retrieve Data | All Rows from the context menu.
2.In the Result grid, click the header of the type column to select all data in it.
3.Right-click the selection and choose Execute template with values | SQLTemplate1.sql.
This command calls the template described previously as many times as there are rows inside the column, and supplies the value of each row as a parameter to the SQL template. As a result, multiple SQL SELECT statements are generated and displayed in the SQL Editor. DatabaseSpy also executes all generated statements, and the result of each is visible in a separate Result grid.
The SQL Template run in this example is only meant for demo purposes. In a real life scenario, you might want to apply the same technique in order to generate more useful statements, for example, ones that insert data into tables. For more details about SQL templates, including an example of how to generate INSERT statements, see SQL Templates.