Geek Answers Handbook

SQL stored procedure for each individual SQL statement in .NET?

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

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

+3
source share
5 answers

Stored procedures have several advantages over parameterized queries:

  • When used exclusively, you can disable CREATE, INSERT, SELECT, UPDATE, ALTER, DROP, DELETE, etc. for your application accounts and thus add a small amount of security.

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

  • Using procedures allows the database administrator to manage and configure queries even after the application is deployed.

  • Deploying small changes and bug fixes is much easier.

They also have several disadvantages:

  • The number of procedures can quickly increase to such an extent that it is difficult to maintain them, and existing tools do not provide a simple method for adequate documentation.

  • Parameterized queries place the database code near the place where it was used. Stored procedures store it far from each other, which makes it difficult to find related code.

  • Stored procedures are more difficult to perform.

You will need to weigh these costs / benefits for your system.

+9
source

Not.

If you send your queries to SQL Server as parameterized queries, SQL Server will cache the execution plan and correctly deactivate your input parameters to avoid SQL injection attacks.

+4
source

I prefer to store procs in embedded SQL, because this way SQL is one consolidated place; however, I prefer to use a tool like nHibernate, which will automatically generate SQL for me, then you do not have SQL to worry about!

+3
source

There is another advantage - when it comes to customization, especially for the client, it can be easily done using SP (by adding hints or even rewriting the code). With embedded SQL, this is almost impossible.

+3
source

This is just one way to do something. Upsides include storing all of your SQL code in one place, checking the procs for syntax at creation time, and the ability to set permissions on procs, which usually represent some kind of “action” and are well suited to the conceptual security model.

The disadvantages include the massive amount of procs for any medium or larger application, and the whole household that comes with it.

My employer product uses procs for everything, and I must say that with good practice, this is perfectly acceptable.

+2
source

More articles:


All Articles