I agree with the security concern on dynamic SQL from CF. This forces SELECT, INSERT, UPDATE, and/or DELETE authorization on the underlying table(s). This is bad or sloppy security (take your pick).
I agree that it is easier to write the dynamic SQL in CF. SQL Server 2000 (TSQL) forces a different coding style. I expect the other major DBs have their own "style". TSQL does not allow an IF statement inside a SELECT. I will stick with a SELECT in my discussion. The TRUE portion of your IF can have a SELECT, and the FALSE portion of your IF can have a SELECT. The developer cannot force the CF code to work in TSQL, unless they are willing to tweak the code. In TSQL there are two solutions to this "dynamic SQL" question. One solution was provided. You build your dynamic SQL into a long string, and then you EXECute that string. Part of this "build" includes one IF test for each possible input variable. The trouble with this solution is that it requires SELECT authorization on the table. So you are doing all this work and still have the security downside. The SQL Server 2000 BOL (Book On-Line) have a good example of this code. BOL is available from the SQL Server 2000 help. The second solution is nested IFs. If you have one input variable, such as Name, then you have two SELECTs. One includes a WHERE clause on that Name column. The other has no WHERE clause on that Name column. If your query has two input variables, then your code will have four SELECTs. Theoretically, one could divide a complicated Stored Procedure (SP) into multiple SPs. With two input variables, you would have five SPs. One is a driver, that contains the IF logic and calls the other four SPs. Each of those SPs has one SELECT statement. Again, BOL has good examples of these concepts. hth -brian > 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:2185 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
