Parameterized query and stored procedure

Question

1.00/5 (1 vote)

See more:

For Database operations like insert, update and delete which is better and fast in terms of performance and security: Parameterized Query or Stored Procedure ? And also when to use DataSet ?

Posted 15-Feb-15 2:31am

A94

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2015-02-15T02:48:00

Parameterized Query vs Stored Procedure
There is no difference in respect to performance or security (almost). SQL server caches query plans for both. You can setup security on schemas/tables the same way as you'd do on stored procedures. Sure SP gives you one more level of abstraction but it's one more thing you need to maintain. Furthermore with SPs you are tempted to put business logic there which you shouldn't. I'd use queries (or LINQ).

Dataset
Don't use that. Have a look on Entity Framework or other ORM. If that's too heavy weight for you there are micro ORMs out there. Having your data in POCOs is priceless.

Wendelius · Accepted Answer · 2015-02-15T04:02:00

In my opinion there is quite a big difference between executing stored procedures versus executing individual statements. In both you should use parameterization so from a single statement point of view the difference is not so big.

However, consider a situation where you need to execute multiple statements in order to get the desired result. If the statements are located on the calling side, you need to execute them separately so each execution means a roundtrip to database and back. This can be time really consuming.

Another scenario: If you have multiple applications which should share the same operation creating a procedure is one way to encapsulate the logic into a single place. Of course in many situations this can also be done on the calling side.

Agnostic to the client: Regardless of the client type, is it a client server application or not, which programming language is used etc the procedure is always the same.

About security, with stored procedures the caller does not need privileges to the actual database objects to do the operation. The caller needs only an execute privilege to the procedure. Depending on the system this can be a huge security benefit.

Parameterized query and stored procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0