Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Stored Procedure
- Thread starter Yura
- Start date
Guest_imported
New member
- Jan 1, 1970
- 0
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>
- Status