Variables and SQL functions

If you use the same identifier for an SPL variable as for an SQL function, the database server assumes that an instance of the identifier is a variable and disallows the use of the SQL function. You cannot use the SQL function within the block of code in which the variable is defined. The example in the following figure shows a block within an SPL procedure in which the variable called user is defined. This definition disallows the use of the USER function in the BEGIN END block.

Figure 1. A procedure that disallows the use of the USER function in the BEGIN END block.

CREATE PROCEDURE user_test()
   DEFINE name CHAR(10);
   DEFINE name2 CHAR(10);
   LET name = user; -- the SQL function

   BEGIN
      DEFINE user CHAR(15); -- disables user function
      LET user = 'Miller';
      LET name = user; -- assigns 'Miller' to variable name
   END
   . . .
   LET name2 = user; -- SQL function again