Contents Index PUT statement [ESQL] READ statement [Interactive SQL]

ASA SQL Reference
  SQL Statements

RAISERROR statement [T-SQL]

Description 

Use this statement to signal an error and to send a message to the client.

Syntax 

RAISERROR error-number [ format-string ] [, arg-list ]

Parameters 

error-number    The error-number is a five-digit integer greater than 17000. The error number is stored in the global variable @@error.

format-string    If format-string is not supplied or is empty, the error number is used to locate an error message in the system tables. Adaptive Server Enterprise obtains messages 17000-19999 from the SYSMESSAGES table. In Adaptive Server Anywhere this table is an empty view, so errors in this range should provide a format string. Messages for error numbers of 20000 or greater are obtained from the SYS.SYSUSERMESSAGES table.

In Adaptive Server Anywhere, the format-string length can be up to 255 bytes.

The extended values supported by the Adaptive Server Enterprise RAISERROR statement are not supported in Adaptive Server Anywhere.

The format string can contain placeholders for the arguments in the optional argument list. These placeholders are of the form %nn!, where nn is an integer between 1 and 20.

Intermediate RAISERROR status and code information is lost after the procedure terminates. If at return time an error occurs along with the RAISERROR then the error information is returned and the RAISERROR information is lost. The application can query intermediate RAISERROR statuses by examining @@error global variable at different execution points.

Usage 

The RAISERROR statement allows user-defined errors to be signaled and sends a message on the client.

Permissions 

None.

Side effects 

None.

See also 

CREATE TRIGGER statement [T-SQL]

ON_TSQL_ERROR option [compatibility]

CONTINUE_AFTER_RAISERROR option [compatibility]

Standards and compatibility 
Example 

The following statement raises error 23000, which is in the range for user-defined errors, and sends a message to the client. Note that there is no comma between the error-number and the format-string parameters. The first item following a comma is interpreted as the first item in the argument list.

RAISERROR 23000 'Invalid entry for this column: %1!', @val

The next example uses RAISERROR to disallow connections.

create procedure DBA.login_check()
begin
    // Allow a maximum of 3 concurrent connections
    if( db_property('ConnCount') > 3 ) then
   raiserror 28000
      'User %1! is not allowed to connect -- there are already %2! users logged on',
      current user,
      cast(db_property('ConnCount') as int)-1;
    else
   call sp_login_environment;
    end if;
end
go
grant execute on DBA.login_check to PUBLIC
go
set option PUBLIC.Login_procedure='DBA.login_check'
go

For an alternate way to disallow connections, see LOGIN_PROCEDURE option [database].

Contents Index PUT statement [ESQL] READ statement [Interactive SQL]