Dynamic SQL in STored Procedures

Hi I have a question, if anyone can answer it. I have a stored procedure that has a number of parameters passed to it. Some or all of these parameters are used in the WHERE clause of the SELECT statement on a FOREACH loop. I was wondering if it was possible to build the SQL statement dynamically in SPL to improve the performance when SELECTing from the database ie the WHERE clause would only use those parameters that have a value specified for them. Thanks in advance Andy H _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

With a stored procedure, no. The execution plan and optimization for a stored procedure is not dynamic and is shared by various user of the stored procedure. However, if you are on 9.x, you could convert the stored procedure to a UDR which could do dynamic SQL. Andy H wrote: > Hi > > I have a question, if anyone can answer it. > > I have a stored procedure that has a number of parameters passed to it. Some > or all of these parameters are used in the WHERE clause of the SELECT > statement on a FOREACH loop. > > I was wondering if it was possible to build the SQL statement dynamically in > SPL to improve the performance when SELECTing from the database ie the > WHERE clause would only use those parameters that have a value specified > for them. > > Thanks in advance > > Andy H > > _____________________________________________________________________________________ > Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

Here is an interesting trick that I picked up a while back that works great: select * from table where (char_col = char_arg or char_arg = "-1") and (int_col = int_arg or int_arg = -1); If the argument has a value then it is used, if the argument is null then the alternative value is assigned. So, as long as your select an impossible alternative value, it works as if your are dynamically building a WHERE clause. Later, Brian "Andy H" <lokidb@hotmail.com> wrote in message news:8vvuua$2rt$1@news.xmission.com... > > Hi > > > I have a question, if anyone can answer it. > > > I have a stored procedure that has a number of parameters passed to it. Some > or all of these parameters are used in the WHERE clause of the SELECT > statement on a FOREACH loop. > > > I was wondering if it was possible to build the SQL statement dynamically in > SPL to improve the performance when SELECTing from the database ie the > WHERE clause would only use those parameters that have a value specified > for them. > > > Thanks in advance > > > Andy H > > > ____________________________________________________________________________ _________ > Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com >