Need Suggestion about Stored Procedure

Question

In web application,windows application what should prefer - Stored procedure or Inline query (Means query written in application)

Aarti · Answer

Why Use Stored Procedures: 1.For any complex data work. If you're going to be doing something truly requiring a cursor or temp tables it's usually fastest to do it within SQL Server. 2.When you need to lock down access to the data. If you don't give table access to users (or role or whatever) you can be sure that the only way to interact with the data is through the SP's you create. Why Use queries: 1.For CRUD when you don't need to restrict data access (or are doing so in another manner). 2.For simple searches. Creating SP's for a bunch of search criteria is a pain and difficult to maintain. If you can generate a reasonably fast search query use that.

Jignesh Trivedi · Answer

yes Of course ......

Ravi Shekhar · Answer

Thank you all. I am very much familiar with stored procedure. I am searching a strong reason to use stored procedure in Web application. Actually i have to explain it to my boss. Currently I am working on windows development. My boss is not prefered for stored procedure for every process. but I often use stored procedure for every process. If I am making stored procedure for DML operation is it not good practice?

Veena Sarda · Answer

Stored procedure is better because of multiple reasons. 1. All your queries are in one place. 2. You can reuse the stored procedure. 3. You can take help of SQL experts to manage your queries for better performance.

Jignesh Trivedi · Answer

Hi, Stored procedure is precompiled and cached so performance is much better. Stored procedure does not always improve performance of your application. Performace is also depend on other parameter. Please refer http://blogs.msdn.com/b/gauravagg/archive/2007/04/27/stored-procedure-vs-sql-query.aspx http://www.codeproject.com/Articles/414272/Stored-Procedures-DO-NOT-increase-performance hope this will help you.

Kailash Chandra Behera · Answer

Hi Ravi, Store procedure is better option in the case of web application, because if you pass inline query, you have to pass the whole query text to the server instead of procedure name. which will increase the data transfer.

Need Suggestion about Stored Procedure

Answers (6)