All Forums Database
darnost 5 posts Joined 08/11
14 Feb 2013
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
 

dnoeth 4628 posts Joined 11/04
14 Feb 2013

Hi Dan,
of course it is:

Select count(*) into TableRowsCount from TeradataTable;

Dieter

Dieter

darnost 5 posts Joined 08/11
18 Feb 2013

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
 

dnoeth 4628 posts Joined 11/04
18 Feb 2013

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

Dieter

KS42982 137 posts Joined 12/12
18 Feb 2013

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

darnost 5 posts Joined 08/11
19 Feb 2013

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

You must sign in to leave a comment.