|
|
|
Forum Member
 
Group: Forum Members
Last Login: 1/25/2007 11:21:00 AM
Posts: 25,
Visits: 1
|
|
Hello everyone,
is it is it possible to display contents of a table through stored procedure?If yes then can some one provide me with the syntax
appreciate your help
Thanks
Regards
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 1/25/2007 11:21:00 AM
Posts: 25,
Visits: 1
|
|
|
I need to pass the table name as a parameter to the stored procedure
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 4:01:17 PM
Posts: 91,
Visits: 118
|
|
No you cannot do this (display the results), but you can create the table in the SP. I've heard V2R7 will have the ability to display the results but I don't know that for sure.
The standard way to do this:
Use the SP to populate a volatile table, then, maintaining the connection, read the table. When the connection closes the volatile table will be dropped.
To create the table using dynamic sql you must be the immediate owner and the creator of the sp (means sp in your own user database). Also, you must have permission to run dynamic SQL.
Hope this helps.
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Today @ 11:39:58 AM
Posts: 328,
Visits: 505
|
|
If the structure of the "returned" table is always the same, Global Temporary tables are easier to use for this purpose than Volatile tables. If the structure varies,
CREATE VOLATILE TABLE ... AS (SELECT ..) ...
may be useful.
If the tablename is a parameter, you'll need to build the SQL statement as a text string and use dynamic SQL [CALL DBC.SysExecSQL(...)], in which case there are some additional restrictions - notably that the creator of the stored procedure must also be the owner of the procedure; e.g. User1 issues "CREATE PROCEDURE User1.MySP ...".
|
|
|
|