Send message to server console from stored procedure

General
Highlighted

Send message to server console from stored procedure

I saw this article: https://community.teradata.com/t5/Viewpoint/How-to-Add-Logging/m-p/64067
Does this have to be implemented in order for me to be able to write a message to the server console when something goes wrong in a stored procedure ?
Are there other ways ?
This example: 

DECLARE CONTINUE HANDLER FOR SQLSTATE ’21000’
-- Label compound statements within handlers as HCS1 etc.
HCS1: BEGIN
INSERT INTO Proc_Error_Tbl
(:SQLSTATE, CURRENT_TIMESTAMP, ’AddBranch’, hMessage);
END HCS1;
DECLARE CONTINUE HANDLER FOR SQLSTATE ’42000’
HCS2: BEGIN
SET hMessage = ’Table Not Found ... ’;
INSERT INTO Proc_Error_Tbl
(:SQLSTATE, CURRENT_TIMESTAMP, ’AddBranch’, hMessage);
END HCS2;

This puts the error message in a table...which doesn't help me.
When running the stored proc from Pentaho, I'd like to see the message in the Pentaho error log which can be seen on the scheduler's console.

With SQL Server, that was easy to do.

 

 


Accepted Solutions
Teradata Employee

Re: Send message to server console from stored procedure

Use the SIGNAL statement to exit the stored procedure and pass an error message to the caller.

 

SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT='Up to 128 characters';

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Send message to server console from stored procedure

Use the SIGNAL statement to exit the stored procedure and pass an error message to the caller.

 

SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT='Up to 128 characters';

Re: Send message to server console from stored procedure

That is so slick Fred....so just "make-up" my own SQL State !!!
I can also do this conditionally, right ?

IF ACTIVITY_COUNT = 0 THEN

    SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT='Up to 128 characters';

END IF

Will the SIGNAL value be shown on the console or do I have to append it to the text message ?
Can 'U0123' be a variable ?

 

Teradata Employee

Re: Send message to server console from stored procedure

Current versions of TD will prefix the message with the procedure name and SQLSTATE value, which must be supplied as a character literal.

The first two characters (class value) should be U0 to indicate a user-defined state, you can pick the last three (subclass value).