People,
I've ventured into the wonderful world of Stored Procedures. My first
experience has been relatively successful however I am stuck on using
host variables to specifiy actualy table or column names in a FROM
clause. After many hours or reading all manner of manuals I've
discovered it appears this is not possible and that in order to so I
need to further venture into dynamic SQL.
My present procedure is based on all static SQL and specifies a numer
of host variables used in IF and WHILE clauses and cursor WHERE
statements. If I have to now use dynamic SQL to get around this nasty
TABLE restriction, is there a way to include such in the same
procedure under different declare statements or am I going to have to
start a completely new learning curve here ?
I guess what I am asking is for a very basic example of how I might go
about using a variable to specify a table name under whatever
circumstances you wish to provide (if at all .. heh).
The variables I use presently are usually set by SELECT INTO
statements, or as the result of a calculation and the reason I need a
vaiable for the table name is it depends on the result of a query in
the proc, therefore not being known at compile time. I take it this
rule also applies to UDFs as well ?
Any rules and limitations providing a brief example would be very much
appreciated. Id very much like to NOT have to completly re-write the
whole thing as it is getting somewhat lengthy.
Procedure is written in a simple SQL CLP script and debugged using
Development Center. System is Windows 2000 server, DB2 v8.1.4 ESE
Many thanks in anticipation,
Tim