Yep, IMHO if you are going down the dyno SQL route you may as well keep it in CF. There are also a load more caveats when using dyno SQL...
As for limitations, well they are numerous, some advanced, some not. I will try to list ones I use all the time for complex operations. You cannot use the TABLE datatype with them which is not good as it is good when you want a speed increase over a #temptable. With dynamic SQL the Query Optimiser cannot (and will not) cache the execution plan so it will always have a greater hit on CPU and memory for enterprise level Applications as it will always have to re-calculate it. Performance tuning - you try and get a decent SQLDiag info block/sp_who/sp_lock or trace with dynamic SQL. You will find it impossible to debug. Caching - for the reason I noted previously. The SQL code is it in own scope, and you have no access to the variables in the calling stored procedure. Any USE statement will not affect the calling stored procedure. Temp tables created in the SQL batch will not be available to the calling procedure. SET statements only affects the SQL batch, not the caller. Termination of the batch executed by sp_executesql terminates the calling procedure too. One key limitation is @@error reports the status of the last statement in the dynamic SQL code - NOT GOOD for error trapping. Even whine faced with the dreaded IN limitation of an SP - you can still get around it without using dyno blocks. I could go on..... ;-) -----Original Message----- From: Stephen Galligan [mailto:[EMAIL PROTECTED] Sent: 25 February 2005 11:06 To: SQL Subject: RE: How do you do an IF condition in where statement Hi Pardeep, I follow the same line if thinking as Neil on this one. One of the other main reasons you should not use dynamic SQL is that you have to allow explicit table level permissions i.e. SELECT, UPDATE etc. This is not a good idea and in a way defeats the object of using the enhanced security aspects that are available in your using of stored procedures...... In my experience I have found you can always convert a dynamic SQL statement into a non-dynamic version with out too much bother. By the way SQL server's Books on line help (BOL) is a great resource when you have a problem. Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 25 February 2005 10:45 To: SQL Subject: Re: How do you do an IF condition in where statement Is it really that bad or hard? ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Friday, February 25, 2005 2:01 AM Subject: RE: How do you do an IF condition in where statement > ARRRGH!!!! Stop using Dynamic SQL ;-)!!!! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2184 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
