sp_autoformat

A utility stored procedure that produces readable result set data, sp_autoformat reformats the width of variable-length character data to display only non-blank characters. Trailing blanks are truncated in the output.

Syntax

sp_autoformat fulltabname[, selectlist, whereclause, orderby]

Parameters

Examples

Usage

  • In SAP ASE version 15.0.3 and higher, sp_autoformat accepts columns of datatypes int (smallint, bigint, tinyint, unsigned int), numeric, money, date/time, and float, real, and double precision.

  • sp_autoformat looks for an object only in the current database. To use sp_autoformat on temporary tables, issue the procedure from tempdb.

  • sp_autoformat does not validate that the columns referenced in any of the parameters actually exist in the table specified by the fulltabname parameter. sp_autoformat fails if you reference any nonexistent columns.

  • Provide only one instance of a column in the select list.

Return codes are:
  • 0 – successful completion

  • 1 – internal error, or usage error in invocation

  • Other – any other errors raised by the SAP ASE server during the execution of the generated SQL statement are returned back to the caller.

Restrictions for sp_autoformat are:
  • sp_autoformat uses internal SQL variables to generate SQL statements that are then executed using execute immediate. The length of the generated SQL statement is limited to 2K bytes. Auto-formatting result sets for a large column list, or columns with long names can sometimes cause an error due to insufficient size of the buffer for the generated SQL statement.

  • Quoted identifiers are not supported for either the table or column names. If you have result sets that use quoted idenfiers and that need autoformatting:

    1. Generate the required data in a temporary table, where the columns in the temporary table do not have any quoted identifiers.

    2. Use sp_autoformat to produce the required output using the temporary table.

    3. Rename the columns in the selectlist in the desired output format.

Permissions

No permission checks are performed for sp_autoformat. Permission checks do not differ based on the granular permissions settings. Users selecting from the tables must have appropriate select privileges.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect