How to store a field value of a table column into variables in Stored Procedure

Database
Enthusiast

How to store a field value of a table column into variables in Stored Procedure

Hi all,

I would like to put a value from a column field int a variable and I cannot find a way how to do it in Stored procedures.

What I would need:

to count number of rows of a table and compare it with a count of a specific value from a specific column of the table.

Therefore would be fine to find a way how to enter output of 'Select count(*) from TeradataTable' to a variable  'TableRowsCount'.

Is that possible at all?

many thanks for help.

Dan

Tags (1)
5 REPLIES
Senior Apprentice

Re: How to store a field value of a table column into variables in Stored Procedure

Hi Dan,

of course it is:

Select count(*) into TableRowsCount from TeradataTable;

Dieter

Enthusiast

Re: How to store a field value of a table column into variables in Stored Procedure

Hi Dieter, thanks a lot. I tried it - a part of the stored procedure is following string:

SELECT COUNT(*) INTO TotalCount FROM CZ_PRD_STG._V2C_CRM_ACCOUNT_R3;

I obtained an error message (LVC_EXP is the name of the procedure):

Executed as Single statement.  Failed [5568 : HY000] LVC_EXP:SQL statement is not supported within a stored procedure.

Sorry to ask anyone again - any idea what's wrong?

Thanks again

Dan

Senior Apprentice

Re: How to store a field value of a table column into variables in Stored Procedure

Hi Dan,

i don't know why it's failing. Are you shure it's this statement?

This should work:

replace procedure cnttest(out cnt int)
begin
declare x int;
select count(*) into x from dbc.tablesV;
set cnt = x;
end;

Dieter

Enthusiast

Re: How to store a field value of a table column into variables in Stored Procedure

By any chance, are you using SELECT...INTO in a dynamic stored procedure ? If yes, then it would not work.

Enthusiast

Re: How to store a field value of a table column into variables in Stored Procedure

Aha,

that's probably one problem. It did not work. I tried another way -  the clause "FROM" contains 'tablename' which was a variable. That does not work too.

Many thanks!

Dan