Stored Procedures - SQL Server

[suarve]

Hi,

Have been told by boss I have to use stored procedures for a particular online shop as I need to adhere to their business rules. I have heard of them before, pretty much know who they work basically but was just curious as to a few things. What are people's opinions of them as opposed to writing out a plain old SQL statement in my code (am usign ASP btw).

Have just been reading a bit about them and I'm told that they offer:

• Consistent, safe data modification
• Network bandwidth conservation

Can some kind soul explain the above two benefits, as I especially don't see how they conserve bandwidth.

Thanks

 

[Dolph]

The first, because you're only writing each procedure once then calling it, means that you are much less prone to data errors from making mistakes within the code. It also means that if you alter/update a procedure, you only have to do it once, rather than finding each SQL statement in code and altering it.

The second, the main benefit is that stored procedures are triggered on the server end, so the data transfer from the client to server is reduced, especially with long, complex SQL code that is reduced to a single line or word (doesn't sound like much, but over a large database with many thousands of queries being run at any time, it makes a huge difference).

 

[Goksly]

They are usually faster as the server can store the execution plan and optimise the query once, instead of every time you call it. Also easier to trace performance issues (can see if one stored proc is killing the server / being called excessive).

My personal opinion is that in all possible cases, use stored procs. If you move to a different platform (maybe your company wants to implement a mobile version of the application) it's a lot easier. Also it adds another layer of abstraction -> the less the application knows of the data structure behind the scenes the better... just get the data and process it

I've not got 10 years experience etc, but this is just what I've picked up. Hopefully it's along the right lines