The choice of choosing stored procedures will not be performance but it will be more from the aspect of security and maintenance. Below are some of the points where stored procedures are definitely a plus over inline SQL.
Abstraction
By putting all your SQL code into a stored procedure, your application is completely abstracted from the field names, tables names, etc. So when you make changes in the SQL, you have less impact in your C# code.
Security
This is the best part where stored procedures again score, you can assign execution rights on users and roles.
Maintenance ease
Now because we have centralized our stored procedures any issue like fixing defects and other changes can be easily done in a stored procedure and it will be reflected across the installed clients. At least we do not need to compile and deploy DLLs and EXEs.
Centralized tuning
If we know we have a slow running stored procedure, we can isolate it and the DBA guys can performance tune it separately.
Cursors, temp table complications
Simple TSQLs are OK. But what if you have a bunch of statements with IF, ELSE, Cursors, etc? For those kind of scenarios, again stored procedures are very handy.
No comments:
Post a Comment