Columnname as parameter to Stored Procedure and do sum on that .

General

Columnname as parameter to Stored Procedure and do sum on that .

Hi,

My requirement is to send Columnname as Parameter to Stored procedure and do SUM on that Column;

I have a table on which i have to do SUM on different feilds ; instead of writing IF statements ; when ever want to do sum just pass column name and do sum;

for ex :
select sum(col1) from table A;
select sum(col2) from table A;

SP :
replace Procedure sample( in name char(20),out summation dec(11,2))
begin
declare sumamount dec(11,2) default 0.00;
select sum(:name) into :sumamount from table A;
set summation = sumamount;
end;

Please advice .

2 REPLIES
Teradata Employee

Re: Columnname as parameter to Stored Procedure and do sum on that .

The following example is one way to achieve that:
replace procedure sum_col
(
in i_column varchar(30)
)
dynamic result sets 1
begin

declare v_dyn_sql_stmt varchar(300) default '';
declare cur_result_set cursor with return only for prpd_stmt_01;

set v_dyn_sql_stmt = 'select sum('||i_column||') from table A;';

prepare prpd_stmt_01 from v_dyn_sql_stmt;
open cur_result_set;
deallocate prepare prpd_stmt_01;

end;

Re: Columnname as parameter to Stored Procedure and do sum on that .

Thanks Joe.

How can i get the value back as output from stored procedure?
Can you please let me know.