Dynamic SQL inside stored procedure

Hi! We are working on maintaining an existing application, with an Informix database. We have some tables that are created and dropped at run time: these are NOT temp tables. All tables have the same column structure, i.e. TableA, TableB and TableC all have exactly the same # of columns, with the same names, in the same order with the same data types. We need to fetch some rows from these tables. **We do not know the table names until runtime**. I was exploring the use of stored procedures in this scenario. Can we pass a tablename across to a stored procedure and have it target the right table dynamically? If we can use a stored proc, would there be any performance gains over sending the full SQL from the client side (ignoring gains from the network)? Thanks a lot. Regards, Bharath. Sent via Deja.com http://www.deja.com/ Before you buy.

<bharathdvs@my-deja.com> a écrit dans le message news:7u2u01$cis$1@nnrp1.deja.com... > Hi! <...> > We have some tables that are created and dropped > at run time: these are NOT temp tables. All. > <...> > We need to fetch some rows from these tables. > **We do not know the table names until runtime**. <...> > Can we pass a tablename across to a stored > procedure and have it target the right table > dynamically? AFAIK stored procedures do not do dynamic SQL - i.e. you can't pass in a table name as a parameter and have SPL build the queries for you. What you _can_ do is make your program create stored procedures on the fly as soon as it knows the table name, I think that one or two people have posted relevant examples on this group recently. > If we can use a stored proc, would there be any > performance gains over sending the full SQL from > the client side (ignoring gains from the network)? <...> Well, I reckon so, but it really depends on a lot of variable factors, like how big the tables are... -- Andrew Pearson - un animal avec beaucoup de fonctions interactives. Parlez et riez ensemble. Il connait 800 mots et bruits. Réagit à la lumière et au bruit. Ses movemements sont très réalistes! Version anglais.

In article <7u2u01$cis$1@nnrp1.deja.com>, bharathdvs@my-deja.com writes >Hi! > >We are working on maintaining an existing >application, with an Informix database. > >We have some tables that are created and dropped >at run time: these are NOT temp tables. All >tables have the same column structure, i.e. >TableA, TableB and TableC all have exactly the >same # of columns, with the same names, in the >same order with the same data types. > >We need to fetch some rows from these tables. > >**We do not know the table names until runtime**. > >I was exploring the use of stored procedures in >this scenario. > >Can we pass a tablename across to a stored >procedure and have it target the right table >dynamically? > No. Dynamic SQl is not supported in stored procedures. >If we can use a stored proc, would there be any >performance gains over sending the full SQL from >the client side (ignoring gains from the network)? > >Thanks a lot. > >Regards, >Bharath. > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams

You can create a synonym to your table before calling SPL function, and work with synonym inside the SPL (always the same name): create synonym syn for TBL23; execute procedure xx() drop synonym syn; create synonym syn for TBL24; execute procedure xx() procedure xx() .. select * from syn ... .. end <bharathdvs@my-deja.com> escribi' en el mensaje de noticias 7u2u01$cis$1@nnrp1.deja.com... > Hi! > > We are working on maintaining an existing > application, with an Informix database. > > We have some tables that are created and dropped > at run time: these are NOT temp tables. All > tables have the same column structure, i.e. > TableA, TableB and TableC all have exactly the > same # of columns, with the same names, in the > same order with the same data types. > > We need to fetch some rows from these tables. > > **We do not know the table names until runtime**. > > I was exploring the use of stored procedures in > this scenario. > > Can we pass a tablename across to a stored > procedure and have it target the right table > dynamically? > > If we can use a stored proc, would there be any > performance gains over sending the full SQL from > the client side (ignoring gains from the network)? > > Thanks a lot. > > Regards, > Bharath. > > > Sent via Deja.com http://www.deja.com/ > Before you buy.