Shaw Shaw - 11 months ago 40
ASP.NET (C#) Question

SQL stored procedure for every single SQL statement in .NET?

Is it a best practice to use stored procedure for every single SQL call in .NET applications?

Is it encouraged for performance reasons and to reduce surface area for SQL injection attacks (in web applications)?

Answer Source

Stored procedures have a few advantages over parameterized queries:

  1. When used exclusively, you can turn off CREATE, INSERT, SELECT, UPDATE, ALTER, DROP, DELETE, etc access for your application accounts, and this way add a small amount of security.

  2. They provide a consistent, manageable interface when you have multiple applications using the same database.

  3. Using procedures allows a DBA to manage and tune queries even after an application is deployed.

  4. Deploying small changes and bug fixes is much simpler.

They also have a few disadvantages:

  1. The number of procedures can quickly grow to the point where maintaining them is difficult, and current tools don't provide a simple method for adequate documentation.

  2. Parameterized queries put the database code next to the place where it's used. Stored procedures keep it far separated, making finding related code more difficult.

  3. Stored procedures are harder to version.

You'll need to weigh those costs/benefits for your system.