Regarding utilizing Stored procedures or just standard SQL in ASP pages. Does anyone know which is more efficient? When is it better to use stored procedures over standard SQL...and WHY? <BR><BR>THANKS in advance!!
This is straight from MS VB6 development material:<BR>Advantages of Stored Procedures<BR>Stored procedures can streamline the execution of a query because of their ability to:<BR>® Accept parameters.<BR>® Return values of parameters to a calling procedure or client.<BR>® Return a status value that indicates success or failure, as well as the reason for the failure.<BR>® Call other stored procedures.<BR>® Encapsulate business functionality so that all applications can use and perform the procedures consistently.<BR>® Make execution faster and more efficient. Once they are compiled no syntax checking is required when they are executed.<BR>® Store the compiled version on the server and use it for subsequent calls, thus reducing network traffic.<BR>® Be called from different client applications.<BR>
but ...<BR>If You have a page where You want to use many parameters, and many of them are optional, then You might reach one point where it is so difficult to write a Stored Proc that You better forget about the advantages and generate a simple plain SQL Statement inside the asp page and pass this to the database. Depends on what You are doing. I am using 80 % SP's and 20 % dynamically generated SQL-Statements.