insert directly or via a stored procedure

I am using sql server and winforms for my application. data would be inserted every minute into the database tables by pressing a button on a Form.

for this, I am using the INSERT query.

开发者_开发知识库

But if I create a procedure and include the same insert query in it, then would it be more efficient, what would be the difference then?

---

Using stored procedures is more secure

---

A stored procedure would generally be quicker as the query plan is stored and does not need to be created for each call. If this is a simple insert the difference would be minimal.

A stored procedure can be run with execute permissions which is more secure than giving insert permissions to the user.

---

It depends on what you mean by 'efficient'.

• Execution time - if you're only saving to the database only every couple of seconds then any speed difference between SPs and INSERT is most likely insignificant. If the volume is especially high you would probably set up something like a command queue on the server before fine-tuning at this level.
• Development time
  • using INSERT means you can write your SQL directly in your codebase (in a repository or similar). I've seen that described as poor design, but I think that as long as you have integration tests around the query there's no real problem
  • Stored Procedures can be more difficult to maintain - you need to have a plan to deploy the new SP to the database. Benefits are that you can implement finer-grained security on the database itself (as @b-rain and @mark_s have said) and it is easy to decide between INSERT and UPDATE within the SP, whereas to do the same in code means making certain assumptions.

Personally (at the moment) I use inline SQL for querying and deleting, and stored procedures for inserting. I have a script and a set of migration files that I can run against the production database to deploy table and SP changes, which seems to work pretty well. I also have integration tests around both the inline SQL and the SP calls. If you go for inline SQL you definitely should use parameterised queries, it helps against SQL injection attacks and it is also easier to read and program.

---

If your DBA is even allowing you to do this without a stored procedure I'd be very suspicious...