Pass table columns as arguments into Teradata SQL stored procedure

Question

For context, I am very new to Teradata SQL and am currently trying to understand how to build out user defined functions (UDF) and procedures (UDP), in SQL form only, though am having some difficulties.

I have created the following UDF to calculate the sum of three inputs

-- 1: how to create the function in the correct database
CREATE FUNCTION db_name.combine_three_numbers (
    num1 FLOAT,
    num2 FLOAT,
    num3 FLOAT
) RETURNS FLOAT
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN num1 + num2 - num3;

Using this, I can call the function on both scalars and columns of a table

-- 2: how to call the function from the database on three scalars
SELECT db_name.combine_three_numbers(5, 10, 3) AS num_comb;

-- 3: how to call the function from the database on three columns
SELECT db_name.combine_three_numbers(tbl_name.col1, tbl_name.col2, tbl_name.col3) AS num_comb;

-- 4: how to remove the function from the database
DROP FUNCTION db_name.combine_three_numbers;

and everything works fine. In 2 it returns a column named num_comb with one row containing a value of 12.0 and in 3 it returns a column named num_comb with multiple rows, where each row value is just a linear combination of the three input columns tbl_name.col1, tbl_name.col2, tbl_name.col3

Next, I tried to perform the same calculation using a stored procedure

-- 1: how to create the procedure in the correct database
CREATE PROCEDURE db_name.combine_three_numbers (
    IN num1 FLOAT,
    IN num2 FLOAT,
    IN num3 FLOAT,
    OUT num_comb FLOAT
)
BEGIN
    SET num_comb = num1 + num2 - num3;
END;

When I call the procedure on scalars

-- 2: how to call the procedure from the database on three scalars
CALL db_name.combine_three_numbers(5, 10, 3, num_comb);

everything works fine, returning the same result as the function equivalent. However, when I try to pass table columns as arguments into a stored procedure

-- 3: how to call the procedure from the database on three columns
CALL db_name.combine_three_numbers(tbl_name.col1, tbl_name.col2, tbl_name.col3, num_comb);

it doesn't work, yielding the error message CALL Failed 5531: (-5531)Named-list is not supported for arguments of a procedure. I am assuming this is because we are unable to pass table columns as arguments to stored procedures, but I can't find anything in the documentation that explicitly says this. So I have two questions

Is it possible to pass table columns as arguments to a stored procedure?
If it is possible, how do I modify my procedure to do this?

If you want to use database objects like columns as arguments, you have to build dynamic SQL in your procedure (actual select statements, columns are not literals) — Andrew
– Andrew, Commented Jan 14 at 14:12
In both cases, the input parameters and output are scalar values not tables. For the UDF, you are actually doing SELECT FROM tbl_name (Teradata allows implicit / omitted FROM clause) and applying the UDF to each row; it's the SELECT that iterates through the table and processes multiple rows. CALL does not allow a FROM clause, just scalar. As @Andrew notes, you can pass a table name or put multiple row input in a fixed table. Stored Procedures can return a "dynamic result set" relation or populate a global temporary table instance if you need to return multiple rows. — Fred
– Fred, Commented Jan 14 at 15:19
@Fred Thanks for your response. Referring to your second sentence, I just wanted to clarify, do you mean that the statement for the UDF SELECT db_name.combined_three_numbers(tbl_name.col1, tbl_name.col2, tbl_name.col3) AS num_comb is actually implicitly calling SELECT db_name.combined_three_numbers(t.col1, t.col2, t.col3) AS num_comb FROM tbl_name AS t ? — Matthew Cassell
– Matthew Cassell, Commented Jan 15 at 3:31
@Fred (cont) And so while a FROM clause is allowed in conjunction with a SELECT clause, it is not allowed in conjunction with a CALL clause, and hence we can't write a statement of the form CALL db_name.combined_three_numbers(t.col1, t.col2, t.col3, num_comb) FROM tbl_name AS t which is why I get an error when passing in tbl_name.col to the procedure? — Matthew Cassell
– Matthew Cassell, Commented Jan 15 at 3:32
@Andrew Thanks for your response. I've actually tried to get dynamic SQL working but to no avail. I'm going through the Teradata documentation again, hopefully I can get something working later today. — Matthew Cassell
– Matthew Cassell, Commented Jan 15 at 4:03

Matthew Cassell · Accepted Answer · 2025-01-20 03:40:55Z

As stated in the comments, because the FROM clause is not allowed in conjunction with a CALL clause, we need to use dynamic SQL to perform the same calculation.

Following the solutions provided here and here and the documentation here, we write the dynamic SQL query as follows

CREATE PROCEDURE db_name.combine_three_numbers_dynamically (
    IN arg1 VARCHAR(32),
    IN arg2 VARCHAR(32),
    IN arg3 VARCHAR(32)
)
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE sql_stmt VARCHAR(128);
    DECLARE rslt CURSOR WITH RETURN ONLY FOR stmt;
    
    SET sql_stmt = 'SELECT ' || arg1 || ' + ' || arg2 || ' - ' || arg3 || ' AS num_comb;';
    PREPARE stmt FROM sql_stmt;
    OPEN rslt;
    
END;

We can call the procedure on both scalars

-- 2: how to call the procedure from the database on three scalars 
db_name.combine_three_numbers_dynamically(5, 10, 3);

and columns of a table

-- 3: how to call the procedure from the database on three columns
db_name.combine_three_numbers_dynamically('tbl_name.col1', 'tbl_name.col2', 'tbl_name.col3');

We drop the procedure just as before

-- 4: how to remove the procedure from the database
DROP PROCEDURE db_name.combine_three_numbers_dynamically;

Collectives™ on Stack Overflow

Pass table columns as arguments into Teradata SQL stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related