Stored Procs

[Rob]

Using VB.net and Sql Server....

In general, should you try to use stored procedures for all updates ?

 

[Paul Clement]

¤ Using VB.net and Sql Server....
¤
¤ In general, should you try to use stored procedures for all updates ?
¤

I'm assuming you're including inserts as well. The preferred method is to use stored procedures but
you should probably use them for select queries as well.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Rob]

Thanks Paul,
Is the primary reason you say so due to controlling permissions, or is it
performance related ?
Rob

 

[m.posseth]

Security ( they prevent sql injection to call one ) and maintanability (
extra tier in the app ) wich will result in independancy of data structure
changes as the app talks to the procedure and not to the underlying tables .

Performance is a big NO ,, a lot of people think that stored procs are
faster but they are not ( precompiled thingy etc etc but for a fact all sql
is compiled in a proc or not , it would go to much in depth to explain this,
so to keep it short a SP is not faster as dynamic sql )


regards

Michel Posseth [MCP]

 

[Paul Clement]

¤ Thanks Paul,
¤ Is the primary reason you say so due to controlling permissions, or is it
¤ performance related ?
¤ Rob
¤

There are a number advantages to using stored procedures. Here a just a few general items off the
top of my head:

1) Security
2) Performance (Tunability)
3) Modularity
4) Reusability
5) Maintainability


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Richard Dudley]

I like stored procedures for a lot of my work, since I can modify them
without recompiling the application. I can also do more sophisticated
queries than from commands generated in code.

However, for a lot of applications, parameterized commands executed from
your code are just as good, and provide you an increased measure of
flexibility. Sprocs lock you into SQL Server, but if you wanted to work
with Access or VistaDB or whatever, you would only need to change the
connectionstring for your OLDEB object and you wouldn't have to write all
sorts of new SQL code. So there's a flexibility consideration that may or
may not figure into what you're doing.