SET Statement

assigning_statement ::= SET target = expression;

This statement evaluates the expression value and assigns it to the target. The target may be a local variable, parameter, client variable, dynamic parameter (PHP, ODBC API) or a column.

If the expression and target types are not the same, the following applies:

If they are both numerical types, an implicit conversion will occur and the error sqlstate 22003 (SQ_NUM_VAL_OUT_OF_RANGE) may occur if the expression value is not the same as the target type.
If they are both character strings, an implicit conversion will occur and the error sqlstate 22001 (SQ_STRING_DATA_RIGHT_TRU) may occur if the expression value is larger than the maximum length of the target.
If one side of the statement is a DATE or TIME type, and the other is TIMESTAMP type, an implicit conversion will occur.
If none of the above apply, an error will occur (incompatible types) when compiling the assign statement.

Note: Conversion function CAST uses explicit conversion.

Example:

Assign a constant to a local variable.

  DECLARE Path CHAR(30);
  DECLARE FileName CHAR(200);
  
  SET Path='p:\mail';
  SET FileName='C:\test.dat';

Example:

Assign a expression value to a table column (explicit conversion).

TRIGGER DateTabControlUpd AFTER UPDATE OF date1 ON Timetab
REFERENCING NEW AS newrow
            OLD AS oldrow
FOR EACH ROW 
BEGIN
  SET newrow.days=CAST(Abs(newrow.date1-oldrow.date1) AS INT);
END

Example:

Assign a expression value to an output parameter.

PROCEDURE `TESTPATH`(INOUT path CHAR(250));
// if the path doesn't end with a slash, add the slash
BEGIN
  IF SUBSTRING(path FROM CHAR_LENGTH(path) FOR 1) <> '/' THEN 
    SET path = path||'/';
  END IF;
END

Block Statement

Control Statements

RETURN Statement