Creating a SQL Script Procedure
This section describes how to create a SQL script—a procedure written in TDV’s SQL script language, as described in “TDV SQL Script” in the TDV Reference Guide.
The parameters you define in the SQL script panel must match the parameters that you define in the Parameters panel, including the order in which they are defined.
To create a SQL script
1. Right-click the container to which you want to add the script, and select New SQL Script.
2. Type a name for the script in the Input dialog, and click OK.
The editor opens in the My SQL Script panel on the right.
For information about the procedure editor, see:
3. Use the SQL Script panel to formulate and edit the SQL script.
— You can use any non-declarative statement as the first statement after BEGIN.
— The first non-declarative statement after BEGIN must not end with a semicolon (;), but every other statement must end with a semicolon.
— If you have a script stored in your file system, you can upload it using the Insert from File button.
— Type the script between the lines BEGIN and END.
— The SQL Script panel can highlight SQL syntax elements (comments, keywords, and so on). You can turn highlighting on and off with the Toggle Syntax Highlighting toolbar button. To change highlighting, select Edit > Options in the main toolbar and open the Editors tab.
— Drag and drop tables, views, or other procedures from the Studio resource tree into the procedure editor panel. This action inserts the full TDV resource name at the drop point.
— Insert standard SQL or C-style comments by highlighting one or more lines and typing:
— Ctrl-Hyphen to insert two dashes (--) at the start of each line.
— Ctrl-/ (forward-slash) to enclose the lines between /* and */.
See
Commenting SQL for information about inserting comments into SQL in TDV.
— Keywords can be used in a SQL script, as long as you enclose them in double quotes; for example:
SELECT “begin” INTO ...
— If you want to do an INSERT, UPDATE, or DELETE operation, you must include the INSERT, UPDATE, or DELETE statement in a SQL script.
4. Save the script.
Here is a sample SQL script. Note the error messages, and the row number and character position fields at the bottom of the panel (separated by a colon).
5. See these sections for more information:
— For information on how to define caching for your procedures, see
TDV Caching.
