solidDB Help : Programming : SQL extensions : Stored procedures : Stored procedure structure : Assigning values to variables
  
Assigning values to variables
To assign values to variables use either of the following syntax formats:
SET variable_name = expression;
or
variable_name := expression;
For example:
SET i = i + 20 ;
i := 100;
For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.
Scalar functions with assignments
A scalar function is an operation denoted by a function name that is followed by a pair of parentheses that enclose zero or more specified arguments. Each scalar function returns one value. Note that scalar functions can be used with assignments, for example:
"CREATE PROCEDURE scalar_sample
RETURNS (string_var VARCHAR(20))
BEGIN
-- CHAR(39) is the single quotation mark/apostrophe
string_var := 'Joe' + {fn CHAR (39)} + 's Garage';
END";
The result of this stored procedure is the output:
Joe's Garage
For a list of solidDB-supported scalar functions (SQL-92), see SQL: Statements.
Note There are some ODBC scalar functions that contain some differences to SQL-92, see ODBC and SQL-92 scalar functions.
Variables, constants, and parameters in assignments
Variables and constants are initialized every time a procedure is executed. By default, variables are initialized to NULL. Unless a variable has been explicitly initialized, its value is NULL, as the following example shows:
BEGIN
DECLARE total INTEGER;
...
total := total + 1; -- assigns a null to total
...
Therefore, never reference a variable before it is assigned a value.
The expression that follows the assignment operator can be arbitrarily complex, but it must yield a data type that is the same data type as the variable, or can be converted to the same data type. When possible, solidDB procedure language implicitly converts data types so that you can use literals, variables and parameters of one type where another type is expected.
Implicit conversion is not possible in the following situations:
▪ information would be lost in the conversion,
▪ a string to be converted to an integer contains non-numeric data.
Examples:
DECLARE integer_var INTEGER; integer_var := 'NR:123';
returns an error.
DECLARE string_var CHAR(3); string_var := 123.45;
results in value "123" in variable string_var.
DECLARE string_var VARCHAR(2); string_var := 123.45;
returns an error.
Single quotation marks and apostrophes in string assignments
Strings are delimited by single quotation marks. If you want to have a single quotation mark within a string, then you can put two single quotation marks (''), side by side, to produce one quotation mark in your output. This is commonly known as an escape sequence. The following stored procedure uses this technique:
"CREATE PROCEDURE q
 RETURNS (string_var VARCHAR(20))
BEGIN
 string_var :='Joe''s Garage';
END";
CALL q;
The result is:
Joe's Garage
Go up to
Stored procedure structure