16.20 - Variables in SQL Requests - Teradata Tools and Utilities

Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems

Teradata Tools and Utilities
September 2019
Programming Reference

SQL statements are most often self-contained in that they convey all the information needed to obtain a response. But it is possible for SQL statements to include variables that do refer to data outside the statement itself. Since data specified within SQL follows SQL syntax, its attributes (whether of numeric or character type, for example) are defined by that syntax. But data supplied outside SQL must explicitly provide its attributes. Teradata SQL provides two ways for an SQL statement to refer to data and its attributes outside SQL: by explicitly named variables, indicated by a colon followed by a name (':a', for example), or for compatibility for IBM's DB2, by a question mark (simply '?')." Insert a second paragraph to convey the following information:

SQL variables of the form ':name' require that a USING row descriptor be prefixed to the SQL statement to provide the attributes of the data and the DBCAREA USING-DATA-POINTER to provide the data.A USING row descriptor imagines that all variables used in that SQL statement form a row in a database table, and the descriptor defines each variable as a column in that row. For example,

	USING(x(INTEGER),y(CHARACTER(1024))...<SQL statement>

provides the attributes for two variables, ':x' and ':y' used in the affixed SQL statement.

SQL variables of the form '?' do not normally refer to a USING row descriptor but instead use the DBCAREA Parameter-descriptor-pointer to provide one DataInfo, DataInfoX, or StatementInformation parcel to define the attributes of all '?' variables, and the DBCAREA USING-DATA-POINTER to provide the data. This is referred to as parameterized SQL.