teradata

Teradata Stored Procedures Parameters- IN , INOUT , OUT

Stored Procedures are basically blocks of SQL code which performs some functionality. However in some cases depending on situation , you may want stored procedure to perform some tasks as per your requirement. Parameters in Stored Procedure helps user to pass some information to Stored Procedure and get the corresponding result.
Teradata Stored Procedure can have 3 types of parameters :
IN: INPUT parameter is used to pass value to Stored Procedures. However
it do not return any value.
INOUT: IN-OUT parameter is used to pass value to Stored Procedures. And it can return any value too.
OUT: OUT Parameter is used to return some value from Stored Procedure only.
Let us see an example below:

REPLACE PROCEDURE CheckParams (IN in_dbname VARCHAR(50), INOUT inout_dbname VARCHAR(50), OUT out_dbname VARCHAR(50))
SQL SECURITY INVOKER
BEGIN
SET out_dbname=’set INOUT value to OUT–>’||inout_dbname;
SET inout_dbname=’set IN value to INOUT–>’||in_dbname;
END;

In the above proc we are setting value of INOUT value to OUT parameter.
And value of IN parameter to INOUT. IN parameter is not returned.

CALL CheckParams (CAST(‘HELLO’ AS CHAR(50)) ,CAST(‘WORLD’ AS CHAR(50)),DBNAME);

Output:

inout_dbnameout_dbname
set IN value to INOUT–>HELLOset INOUT value to OUT–>WORLD

Leave a Reply

Your email address will not be published. Required fields are marked *