if...else

Imposes conditions on the execution of a SQL statement.

Syntax

if logical_expression [plan "abstract plan"]
	statements
[else 
	[if logical_expression] [plan "abstract plan"]
		statement]

Parameters

Examples

Usage

  • The statement following an if keyword and its condition is executed if the condition is satisfied (when the logical expression returns TRUE). The optional else keyword introduces an alternate SQL statement that executes when the if condition is not satisfied (when the logical expression returns FALSE).

  • The if or else condition affects the performance of only a single SQL statement, unless statements are grouped into a block between the keywords begin and end (see Example 3).

    The statement clause can be an execute command or any other legal SQL statement or statement block.

  • If a select statement is used as part of the Boolean expression, it must return a single value.

  • if...else constructs can be used either in a stored procedure (where they are often used to test for the existence of some parameter) or in ad hoc queries (see Examples 1 and 2).

  • if tests can be nested either within another if or following an else. The maximum number of if tests you can nest varies with the complexity of any select statements (or other language constructs) that you include with each if...else construct.

    Note: When an alter table, create table, or create view command occurs within an if...else block, the SAP ASE server creates the schema for the table or view before determining whether the condition is true. This may lead to errors if the table or view already exists.
  • If you create tables with varchar, nvarchar, univarchar, or varbinary columns whose total defined width is greater than the maximum allowed row size, a warning message appears, but the table is created. If you try to insert more than the maximum number bytes into such a row, or to update a row so that its total row size is greater than the maximum length, the SAP ASE server produces an error message, and the command fails.

Note: When a create table command occurs within an if...else block or a while loop, the SAP ASE server creates the schema for the table before determining whether the condition is true. This may lead to errors if the table already exists. To avoid this situation, either make sure a view with the same name does not already exist in the database or use an execute statement, as follows:
if not exists
     (select * from sysobjects where name="my table")
begin
execute ("create table mytable (x int)")
end
For dynamically executing Transact-SQL:
  • When used with the string or char_variable options, execute concatenates the supplied strings and variables to execute the resulting Transact-SQL command. This form of the execute command may be used in SQL batches, procedures, and triggers.

  • You cannot supply string and char_variable options to execute the following commands: use, exec(string) (not the execute stored procedure), connect, begin transaction, rollback, commit, and dbcc.

  • string and char_variable options can be concatenated to create new tables. Within the same SQL batch or procedure, however, the table created with execute is visible only to other execute commands. After the SQL batch or procedure has completed, the dynamically created table is persistent and visible to other commands.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

No permission is required to use if...else.

Related reference
if...else
while
begin...end
create procedure