Comments

InterSystems SQL supports both single-line comments and multi-line comments. Comment text can contain any characters or strings, except, of course, the character(s) that indicate the end of the comment.

Note:

Using Embedded SQL marker syntax (&sql<marker>(...)<reversemarker>) imposes a restriction on the contents of SQL comments. If you are using marker syntax, the comments within the SQL code may not contain the character sequence “)<reversemarker>”. For further details, refer to The &sql Directive.

You can use the preparse()Opens in a new tab method to return an SQL DML statement stripped of comments. The preparse() method also replaces each query argument with a ? character and returns a %List structure of these arguments. The preparse() method in the following example returns a parsed version of the query, stripped of single-line and multi-line comments and whitespace:

  SET myq=4
  SET myq(1)="SELECT TOP ? Name /* first name */, Age "
  SET myq(2)="   FROM Sample.MyTable -- this is the FROM clause"
  SET myq(3)="   WHERE  /* various conditions "
  SET myq(4)="apply */ Name='Fred' AND Age > 21 -- end of query"
  DO ##class(%SQL.Statement).preparse(.myq,.stripped,.args)
  WRITE stripped,!
  WRITE $LISTTOSTRING(args)

Single Line Comments

A single-line comment is specified by a two-hyphen prefix. A comment can be on a separate line, or can appear on the same line as SQL code. When a comment follows SQL code on the same line, at least one blank space must separate the code from the double-hyphen comment operator. A comment can contain any characters, including hyphens, asterisks, and slashes. The comment continues to the end of the line.

The following example contains multiple single-line comments:

-- This is a simple SQL query
-- containing -- (double hyphen) comments
SELECT TOP 10 Name,Age, -- Two columns selected
Home_State -- A third column
FROM Sample.Person -- Table name
-- Other clauses follow
WHERE Age > 20 AND -- Comment within a clause
Age < 40
ORDER BY Age,  -- Comment within a clause
Home_State
-- End of query

Multiple Line Comments

A multiple-line comment is specified by a /* opening delimiter and a */ closing delimiter. A comment can appear on one or more separate lines, or can begin or end on the same line as SQL code. A comment delimiter should be separated from SQL code by at least one blank space. A comment can contain any characters, including hyphens, asterisks and slashes, with the obvious exception of the */ character pair.

Note:

The syntax /*#OPTIONS */, with no space between the /* and the #, specifies a comment option. A comment option is not a comment; it specifies a code option that the query optimizer uses during the compile of the SQL query. A comment option is specified using JSON syntax, commonly a key:value pair such as the following: /*#OPTIONS {"optionName":value} */.

The following example contains several multiple-line comments:

/* This is 
   a simple 
   SQL query. */
SELECT TOP 10 Name,Age /* Two fields selected */
FROM Sample.Person  /* Other clauses 
could appear here */ ORDER BY Age
/* End of query */

When commenting out Embedded SQL code, always begin the comment before the &sql directive or within the parentheses. The following example correctly comments out two the Embedded SQL code blocks:

   SET a="default name",b="default age"
   WRITE "(not) Invoking Embedded SQL",!
   /*&sql(SELECT Name INTO :a FROM Sample.Person) */
   WRITE "The name is ",a,!
   WRITE "Invoking Embedded SQL (as a no-op)",!
   &sql(/* SELECT Age INTO :b FROM Sample.Person */)
   WRITE "The age is ",b

SQL Code Retained as Comments

Embedded SQL statements can be retained as comments in the .INT code version of routines. This is done system-wide by setting the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("RetainSQL",1,.oldval). To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays the Retain SQL as Comments setting. The default is 1 (“Yes”).

Set this option to “Yes” to retain SQL statements as comments in the .INT code version of a routine. Setting this option to “Yes” also lists all non-% variables used by the SQL statements in the comment text. These listed variables should also be listed in the ObjectScript procedure’s PUBLIC variable list and re-initialized using the NEW command. For further details, refer to Host Variables.

Logical Operators