| Author |
Topic |
|
tariq_erp
Starting Member
9 Posts |
 Posted - 2008-02-20 : 00:30:32
|
| Hi AllCan anyone explain me what are the benefits of using SQL stored procedures and database functions instead writing inline queries and command in the code. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-20 : 00:59:43
|
| Performance and security are the biggest reasons for me.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-20 : 01:16:40
|
| Using strored procedures & functions also makes maintainability easy and increases the reusability of the code logic. |
|
|
|
tariq_erp
Starting Member
9 Posts |
Posted - 2008-02-20 : 06:02:54
|
| thanx for the response.I also knew this but require a little more elaboration .. HOW? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-20 : 06:11:16
|
| Suppose you have a logic which you are writing as a inline query ex, some string manipulation. At a later stage, if you require the same logic to be applied you again need to code it out. If you are making a function with the logic and using parameters for involved fields, you can call the same function wherever you want to perform function by passing the required values as parameters. This is how you can increase reusability.Also, consider you wriing an inline query and at a later stage you want to change. With each change to your original logic, you need to open your application and change the inline query. But if you are using a stored procedure, whatever change you need to do requires only changes to sp and you dont require to open up your application and do the change. Also if you want same results at some other place, you can simply call this sp to retrieve the resul;ts, rather than repeating the entire query as such. This is how you can improve maintainablilty. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2009-12-22 : 08:59:07
|
| After reading those 2 articles it appears most people dislike them and find them more difficult to maintain...sounds like the benefits are marginal at best. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-12-22 : 10:09:56
|
| Stored Procedures are good for Deadlock analysis.When there is a Deadlock, SQL Server will report the query and the Stored Procedure, if one is involved. If you didn't use Stored Procedures, the Deadlock report will say 'Adhoc', now good luck in finding where that originated from! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-22 : 10:10:16
|
| Here's a benefit:Your application is live serving x thousands of people and someone finds a major bug with one of the queries. With an add hoc query in the application then you are facing a merge or rebuild of your application. With a stored proc you are facing an ALTER PROC statement.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-22 : 10:12:47
|
quote:
Originally posted by denis_the_thief
Stored Procedures are good for Deadlock analysis.When there is a Deadlock, SQL Server will report the query and the Stored Procedure, if one is involved. If you didn't use Stored Procedures, the Deadlock report will say 'Adhoc', now good luck in finding where that originated from!
Amen. I've got an intermittent deadlock issue now and tracking it down is a nightmare! Got a server side trace running and I'm having to bring back the SQL:statement starts text just to try and figure out what the hell is going on. This is producing gigs and gigs of data which I'm just having to bin until I catch a deadlock break.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-12-22 : 10:37:03
|
quote:
Originally posted by Transact Charlie
Here's a benefit:Your application is live serving x thousands of people and someone finds a major bug with one of the queries. With an add hoc query in the application then you are facing a merge or rebuild of your application. With a stored proc you are facing an ALTER PROC statement.
I agree that certainly is a big benefit.But that is like a double-edged sword too. If you have one big Application without in-line queries plus 1,000 stored procs, this like 1 big program plus 1,000 little programs. So at Release time, with Stored Procedures, you'll have issues like: out of these 1,000 little programs, which ones does the Client need? which ones changed? which ones have been tested? which ones are still under construction? how will these be deployed? Sometimes with an application with in-line queries the Release is simple: build the exe and copy it where it needs to go. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-22 : 11:03:50
|
depends on your environment I guess.We've got an application layer servicing many database and unfortunately because our platform is very flexible it's very hard to test all possible scenarios. By 'bug' I didn't really mean that the application ended up throwing an exception or stop working (those kind of scenarios should be caught by good unit testing) - I more meant that whatever logic was going on was either:Not what was agreed with the clientWas agreed but the client didn't understand what they wantedIsn't doing what it should but still 'works' - doesn't throw an errorTakes a very long time to do what it needs to do In those situations its so, so much easier for me to find the problem piece or badly performing sql in a stored proc and rewrite the procedure to either the orriginal requirement or the clients revised 'MUST HAVE NOW' design. I can then drop that code into the database of the client that is complaining so that they see the benefit straight away and so that we don't run the risk of breaking the live application for other clients.The platform is in continual development with a monthly release cycle so if an issue arises that actually needs an application code change (recently the dev's here have been hitting the hibernate sauce a lot) then either the client has to wait for the changes to be accepted and tested or we have to devote a lot of extra work to deploying the 'fix' early onto the testing environments and getting someone to test that the changes don't break live.From a support point of view I *much* prefer being able to modify a stored procedure rather than having to rebuild out application, redeploy our application and I definitely prefer not having to run the risk of the build failing for some random reason.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Benifits of using stored procedures and functions