Re: Raise signal with dynamic message_text

Serman D. wrote:
> Environment is DB2/LINUX 9.5.0.
> 
> I wonder if it is possible to set the MESSAGE_TEXT dynamically when
> using the SIGNAL statement.  The objective is to return more specific
> information to the calling user or application. E.g. I would rather
> return "Unknown value 42" than just "Unknown value".
> 
> The do***entation says:
> 
>
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004232.htm
> 
>   SET MESSAGE_TEXT =
> 
>   diagnostic-string-expression
> 
>   An expression of type CHAR or VARCHAR that returns a character
>   string of up to 70 bytes to describe the error condition. If the
>   string is longer than 70 bytes, it is truncated.
> 
> So i thought something like this should work, but it does not (for me,
> at least):
> 
> CREATE PROCEDURE customerror ()
> BEGIN
>   DECLARE v_value CHAR(5) DEFAULT 'wrong';
>   DECLARE c_unknown CONDITION FOR SQLSTATE '90001';
>   SIGNAL c_unknown SET MESSAGE_TEXT = ('Unknown value ' | v_value);
> END
> @[EMAIL PROTECTED]
> 
> Is it possible to set the MESSAGE_TEXT dynamically?
Of course. Simply compose the text in a local variable and then use that 
variable in the SIGNAL.

Cheers
Serge
-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab