Board index » delphi » Table Name in store procedure

Table Name in store procedure

Hi,
   Can i pass in a table name in stored procedure thru a variable, then i
use the variable in a select statement.

    For eg:
set term !! ;
Create procedure Get_Total_Records
  (
    I_Table_Name varchar(50)
  )
returns
  (
    O_Rec_Cnt integer
  )
as
begin
  for
    select count(*) from I_Table_Name
    into O_Rec_Cnt
  do
    suspend;
end !!
set term ; !!

commit;
When i try to create this stored procedure, it give me error table unknown.
Can anyone help me to solve this problem?
TQ

 

Re:Table Name in store procedure


No you can't do that.  Column and table names can not be variable substitutions.

Quote
poolan wrote:

> Hi,
>    Can i pass in a table name in stored procedure thru a variable, then i
> use the variable in a select statement.

--
Jeff Overcash (TeamB)   I don't think there are any Russians
(Please do not email    And there ain't no Yanks
 me directly unless     Just corporate criminals
 asked.  Thank You)     Playing with tanks.  (Michael Been)

Re:Table Name in store procedure


poolan <pl...@tonecorp.com> a crit dans le message : 3b5639e3_2@dnews...

Quote
> Hi,
>    Can i pass in a table name in stored procedure thru a variable, then i
> use the variable in a select statement.

>     For eg:
> set term !! ;
> Create procedure Get_Total_Records
>   (
>     I_Table_Name varchar(50)
>   )
> returns
>   (
>     O_Rec_Cnt integer
>   )
> as
> begin
>   for
>     select count(*) from I_Table_Name
>     into O_Rec_Cnt
>   do
>     suspend;
> end !!
> set term ; !!

> commit;
> When i try to create this stored procedure, it give me error table
unknown.
> Can anyone help me to solve this problem?

You can't use variables as field or table names in stored procs. Sorry.

Berenger Enselme

Other Threads