{sql} Directive

Delimits the SQL section

Syntax

Details

Overview of the SQL Section

SQL for a Query Section

SQL for an Update Section

Example

Syntax

{sql <empty="success"|"error"> <error="noprint">}...{/sql}

Optional Arguments

empty="success"|"error": The value for empty= specifies whether processing transfers to the success section (empty="success") or error section (empty="error") if the SQL section resolves to an empty section. The default value is error.

Note that this parameter is used only for SQL sections that are within update sections.

error="noprint": Specify error="noprint" if you want to suppress error messages that are produced by htmSQL during SQL processing.

Note that if you do not specify this option and your SQL statement contains a SAS data set password, then you risk exposing the password because htmSQL includes the SQL statement along with the SQL error message.

Details

Overview of the SQL Section

The {sql} and {/sql} directive pair delimits the SQL section. The SQL section is a part of both the query and update sections and contains the SQL statements to be sent to the SAS/SHARE server. You can use any SQL statement that is supported by the SAS SQL processor. You can have only one SQL statement per SQL section, but you can have multiple SQL sections within both the query and update sections.

For more information about using SQL statements with SAS data, see the Base SAS Procedures Guide.

SQL for a Query Section

In a query section, the information between the beginning and ending {sql} directives must begin with the SELECT keyword and must contain one valid SQL query.

An SQL query can be either static or dynamic.

If you want each of your users to use the same query every time they access your Web page, write a static query. Static queries consist of expressions and clauses that contain constant values and no variable references.
With a dynamic query, the users of your Web page can customize the query by specifying their own values for search parameters. The query is written using variable references that are given values when users access the Web page.
For example, if your data contains a DATE column, and you want users to specify their own dates to search on, you can place a variable reference in the query for DATE. The following example illustrates this query:
```
{sql}
   select NAME, TITLE, DEPT from EMPDB.EMPLOYEE
      where START='{&DATE}'
{/sql}
```
The values that users provide can be specified on the htmSQL URL or collected from an HTML form that you link them to. If you nest a query in the eachrow section of another query section, your nested query can refer to variables in the results set of the encompassing query.

SQL for an Update Section

In an update section, the information between the beginning and ending {sql} directives must begin with the ALTER, CREATE, DELETE, DROP, INSERT, or UPDATE keyword and must contain one valid SQL statement.

Note: The Webmaster can disable ALTER, CREATE, DELETE, DROP, INSERT, and UPDATE statements by specifying the READONLY option in the htmSQL configuration file.

Example

The following example SQL sections are placed consecutively in an update section. The first section creates a data set named def.play, and the second section inserts values into it:

{sql}
   create table def.play
      (a numeric, b numeric, c numeric, d char, e char);
{/sql}

{sql}
   insert into def.play
      set a=1, b=2, c=3, d='xx', e='yy';
{/sql}