IBM Support

Printing a message or a variable value within a stored procedure

Question & Answer


Question

How do I print a message or the value of a variable within a stored procedure?

Answer

When a stored procedure evaluates an expression, you may want to check the value of that variable at different stages of execution. In addition, you may want to print messages in procedure output. To accomplish these tasks, use the RAISE NOTICE statement in the stored procedure, as shown in the following example:
CREATE OR REPLACE PROCEDURE expression_eg() RETURNS BOOL LANGUAGE NZPLSQL AS
  
BEGIN_PROC
  
DECLARE
NUMERIC;
BEGIN
n := 2147483647;
RAISE NOTICE 'Original value';
RAISE NOTICE 'n is %', n;
RAISE NOTICE 'Modified value';
n := 2147483647 + 1;
RAISE NOTICE 'n is %', n;
END;
  
END_PROC;


Output of the example is as follows:
host(admin)=> CALL expression_eg(); 
NOTICE:  Original value
NOTICE:  n is 2147483647 NOTICE:  Modified value
NOTICE:  n is -2147483648  
expression_eg
---------------
(1 row)

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ884047

Document Information

More support for:
IBM PureData System

Software version:
1.0.0

Document number:
463555

Modified date:
17 October 2019

UID

swg21571334