Stored Procedures behaving dynamic ?

[rasalom]

Hi everybody.

I got a problem with Interbase 6.0.

What I would like to do is write a Stored Procedure which can be run on different tables. What I mean is that I want
to pass the table's name as a parameter to the procedure which then itself accomplishes a certain task on that specific table. To make things clearer a little bit, I give you an example of what i mean:

create procedure test (
tablename varchar(32)
) returns (
number integer
) as
begin
select numbercolumn from :tablename into :number;
suspend;
end

Unfortunately, Interbase doesn't seem to support this type of table-dynamic select (or insert or update) statements
and I think that is because of the dependencies between database objects, which would not be clear then anymore.

Anyway, it would be of great help to have such a construct, because I have to implement quite a large procedure
which has to do the same task for about 15 or 20 tables and I really don't want to code it for each table for
reasons that are obvious as i think
It would be much more elegant and effective if I had the possibility to implement the procedure only once in a way
similar to the one above, that could work with many different tables. So I would be really grateful if someone had
an idea of how to realize a Stored Procedure that behaves like the above one would. Maybe there are some tricks
that would help to realize it somehow.


I hope I have made my problem clear and am really looking forward to all your replies...

thanks in advance

 

[Otto]

Hi!

What about creating the sp (with the desired table name) in 'run-time' from your host application (ie. Delphi) before running it?
I have used this 'trick' and it's working well.

Otto

 

[rasalom]

Hi.

Yeah I have thought about that too and I guess that is the solution I am gonna use if nobody knows something with a more "elegant" touch

But anyway I thought there might be a solution that is more graceful, because creating/deleting the procedure dynamically from the host application over and over makes the project more complex and not as straight-forward as it would be with a static procedure.
This got relevance especially for people who have to deal with it in the future for programming further features or things like these, so I'd prefer getting it "clean" and straight-forward IF there is a way how to accomplish it.


But anyway thank you very much for your reply

 

[Oppenhiemer]

Hi..

Dont have a complete solution for you, but maybe look at statements such as :

DECLARE STATEMENT
EXEC SQL
and
EXECUTE

They seem to offer the facility to declare and use dynamic sql statements in stored procedures. However, I have found very few useful examples of their usage (seems to be a lack of practical examples - and the Inerbase PDF/Help files are quite vague - poor.)

The other option would perhaps to look at creating UDF's and calling these from Interbase ?

Hope this helps, and would be interested in hearing if you find a solution.

Cheers..

Opp.

 

[MartijnTonies]

Oppenheimer, those aren't possible in a stored procedure.

InterBase doesn't support dynamic statements.


Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[Oppenhiemer]

Ahh.. that would explain perhaps why I cant seem much info on them except in the context of UDF's then.

So looks like a UDF or front-end parsing is the only option for dynamic Stored procedures then ?

Cheers..

Opp.

 

[MartijnTonies]

UDFs - no. They don't (or shouldn't) do SQL.

As for front end stuff - yes, that's the only way.

Those EXEC SQL and so on are used for pre-processes embedded SQL.



Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[rasalom]

Yeah well i have tried to fool around with those EXECUTE statements too but like Marijn says, in Stored Procedures these are not allowed. These statements are used for input to gpre the preprocessor as far as I understood, but since i am using Delphi this wont be much of help.

Anyway, the option that seems to be possible is indeed the dynamic creation/deletion of the currently needed Stored Procedure by the host application.

In my opinion this is very inconvenient tho

Another option (which is just as bad in my opinion) is to create the Stored Procedure statically and implement it with a giant "if"-clause and then I pass a number to the SP which will then decide by the number which table is meant and branch into the specific part for that number/table then.

The mess is that you have to do that also for subprocedures which are supposed to be called from the main procedure, so you finally will get a whole chaotic mess of ultralong procedures... not the most elegant way to do it is it ?

anyway, thanks you all for your help.

bye