Michael Neuber wrote : >Hi 2 all! >A couple of days ago I read in some of the messages here >how someone tried to pass names of <db-user name> and <table name> >to a stored procedure and this way make it more flexible >when it comes to target tables which are identical in structure, >but might have been distinguished by their names, >even might reside in different user spaces >(might sound strange, but there's indeed need for such things).
>Unfortunately I could not relocate this example and the SAPdb team's >comments on that, instead I found indications that >DBPROCs need information on user and table name as *identifiers*, >not as *parameters*. This would force me to create the DBPROC declaration >elsewise and declare it on the fly before using it, and, if need be, destroy it >afterwards. >a) Is it still true that DBPROCs don't accept <username> and <tablename> as >parameters? You can pass <username> and <tablename> as parameters to a db-procedure, but you cannot use them as table identification in static SQL contained in the procedure, i.e. the following is not possible : create dbproc P(in owner char(32), in tablename char(32)) as select * from :owner.:tablename; I thinks that's what you're asking for. So the answer is yes. >b) Are there plans to extent the DBPROC functionality in this direction? no. >c) In case this restriction is not yet lifted: Did anyone manage to find a neat >workaround ? The workaround will be dynamic SQL, i.e. you create the SQL statements to be executed at runtime of the db-procedure. 7.3.0.29 already contains the possibility to create and execute simple SQL statements at runtime. Example : create dbproc P(in owner char(32), in tablename char(32)) as VAR stmt char(100); stmt = 'SELECT * FROM ' || owner || '.' || tablename; execute stmt; Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
