Stored Procedures - Whats the big deal?

[John Cassell]

Hello,

I am still fairly new to VB and SQL and I have always ignored it when I have seen "Stored Procedures" mentioned. Today I stumbled across an explanation of it and it claimed to retrieve data from SQL much faster and more efficiently than normal queries so I suddenly became very interested..

What it didn't tell me was how much of a benefit it would give me and I was wondering in what parts of my app should I be thinking of using it?

For example, my app will eventually include a reporting function and a likely scenario would be to retrieve hundreds of jobs from a table containing thousands of records...

I guess this would be a good place to use a stored procedure instead of a normal select statement but I also have very small requests to the DB such as telling me whether a particular user is logged in or not.

Could someone just give me a bit of advice on this please?

Thanks very much

John

 

[JohnH]

There were a few related blog posts about this 4 years ago, and much discussion along with them:
Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome)
Stored procedures are bad, m'kay?
Who Needs Stored Procedures, Anyways?

 

[giadich]

in a few words, stored procedures offer security, performance improvement, prevent sql injection, encryption, handle complex logic... it's not hard to pick up and once you do, you might like it.

 

[John Cassell]

Hi again,

Thanks for that, much appreciated,
JohnH - I actually started readying the 'SP are bad m'kay' last night but thanks for the other items I'll give them a read.

giadich - thanks for the nutshell, just looked up those sql injections and they are very cheeky aren't they! - i don't think for one second my users would be able to intentially do an injection but it is very good to know that problem exists.

Cheers

John