----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: SriSamp Message 3 in Discussion There are some pretty simple reasons why stored procedures are better than ad-hoc queries. Every query that is executed by SQL Server has to undergo some phases. One of the phases is that of creating an execution plan. If you submit a series of queries to SQL Server, these execution plans have to be created each time. If on the other hand, you use SP's, the execution plan is created up-front (during creation) and used each time. Bottom-line is performance is improved using SP. Queries being sent over the network use a large amount of bandwidth, but SP calls are very simple and just comprise the name of the procedure and a set of parameters. Bottom-line is network band width is reduced with Sp's. By having your application send queries to SQL Server, you are increasing the maintenance and distribution cost of your application. A change in a query will force you to update application code. If you use SP's, the code is very well encapsulated. Bottom-line is maintenance improves with SP. By having your application issue queries to SQL Server, you need to give permissions over tables to users. This can have adverse effects on your application, since any kind of query can be sent to SQL Server. If you use stored procedures, you can effectively mask security to only the SP and users see a well abstracted layer to your system through the SP. Only execute permissions on the SP are required. Bottom-line is security is improved using SP's. By having SQL statements sent over to SQL Server from your application, it is very easy for hackers to compromise the system by doing what is called SQL Injection. SP's let you avoid this problem. Bottom-line is system security is protected by using SP's. There are many other smaller advantages of using SP's over queries. Personally, I see no reason why an application should send queries directly to SQL Server and this should be avoided at all costs. HTH, Srinivas Sampath MVP - SQL Server http://www32.brinkster.com/srisamp ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
