You can use the EXECUTE command to run a SQL statement built from concatenated strings. Assumming you've got the sample pubs database installed, try this:<br><br><FONT FACE=monospace><br>use pubs<br>go<br><br>create procedure SelectStarFrom<br>(<br> @TableName varchar(255)<br>)<br>as<br>execute('SELECT * FROM ' + @TableName)<br><br>go<br><br>exec SelectStarFrom 'authors'<br>go<br><br></font><br><br>Note that it is best to use this method only when flexibility is more important than performance. Anytime you use dynamically constructed SQL like this the server will need to take a fraction of a second to recompile the query plan relative to the table name you have supplied. That's no problem if you're building an administrative procedure for occasional use, but you'd want to avoid the technique in areas of an application that will be used intensively. <br>
In Oracle (version 7 or best) you can use DBMS_SQL package to construct dynamicaly any query or procedure you want.<br>example:<br>procedure my_proc(my_table VARCHAR2) IS<br>dummy NUMBER;<br>cid NUMBER;<br>counter NUMBER:= 0;<br><br>begin<br>cid:= DBMS_SQL.OPEN_CURSOR;<br>DBMS_SQL.PARSE(cid,"SELECT * FROM "¦¦my_table,DBMS_SQL.V7);<br>dummy:= DBMS_SQL.EXECUTE(cid);<br>WHILE DBMS_SQL.FETCH_ROWS(cid)=0 LOOP<br> counter:=counter+1;<br> DBMS_OUTPUT.PUT_LINE("Row number"¦¦TO_CHAR(counter));<br>END LOOP;<br>DBMS_SQL.CLOSE_CURSOR(cid);<br>EXCEPTION<br> WHEN OTHERS THEN<br> IF DBMS_SQL.IS_OPEN(cid) THEN<br> DBMS_SQL.CLOSE_CURSOR(cid);<br> END IF;<br>END my_proc; <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.