July 24, 2010 at 7:50 am
We are redeveloping our product from SQL Server 2000 to SQL server 2008(Can go for any RDBMS in future). We have design an application which designs query forms dynamically, which works fine. Now we are in the stage where we are composing the SQLs to fetch the data from underlying table/view/table function the dynamic form generation will only apply filters to these entities. This is our basic requirements. Now we are discussing about the prons and cons of Ad-hoc SQL vs Stored Procedures for this development.
Can anyone guide us about this? We are using SQL server 2008, so expecting any improvements in SQL server 2008.
Regards,
Mangesh
July 25, 2010 at 10:50 pm
If you choose to go with dynamic SQL, be sure that it is parameterized dynamic SQL, using sp_executeSQL. You can find specifics and examples in BOL. Parameterized dynamic SQL gives you a greater measure of protection against SQL injection attacks, and will also cache execution plans for the queries you generate.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 26, 2010 at 10:55 am
patil.mangesh.v (7/24/2010)
We are redeveloping our product from SQL Server 2000 to SQL server 2008(Can go for any RDBMS in future). We have design an application which designs query forms dynamically, which works fine. Now we are in the stage where we are composing the SQLs to fetch the data from underlying table/view/table function the dynamic form generation will only apply filters to these entities. This is our basic requirements. Now we are discussing about the prons and cons of Ad-hoc SQL vs Stored Procedures for this development.Can anyone guide us about this? We are using SQL server 2008, so expecting any improvements in SQL server 2008.
Regards,
Mangesh
If you want to make your system flexible enough to port it to another RDBMS, then writing stored procs to access DB may not be the best design approach.
Look into using some ORM tool. I would suggest NHybernate as it is most flexible, relable and maintainable one... (plus it is a free one, with source evailable you may tune it into whatever you want!).
Read about all pros and cons of using ORM before makling final final decision. It may be a good idea to call relevant consultant to help you out...
July 28, 2010 at 6:29 am
Thanks.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply