Stored Procedures or Direct SQL Statements
-
Hi, I wonder which is better using direct sql statments directly in the code or writing stored procedures inside the SQL Server database? What are the advantages and disadvantages of each? Thank you
stored procedures are compiled and operated much faster than free line code. Store procedures are also more secure Store procedure also let you modify the back end business logic without effecting your web or client applicated. Store procedures can be unit tested as a seperate business logic. Store procedure can return scaler values, tables , or action as action code. Store procedures can have error trapping and rollbacks if multiple tables need to be updates. Don't use free line SQL unless you can avoid it.
-
Hi, I wonder which is better using direct sql statments directly in the code or writing stored procedures inside the SQL Server database? What are the advantages and disadvantages of each? Thank you
Stored Procedures are better in my opinion. Here are a couple of reasons: SPs mean that you can revoke the permissions on direct table access pushing everything through SPs. This means the only actions that an application can perform are only the actions the SPs permit. SPs can do additional checking of the data. This allows the database to weed out potentially dangerous data as part of a layered security approach. e.g. The application weeds out potentially harmful data first, but if that security layer should be compromised somehow the database itself has a layers of security to prevent dangerous actions - this includes logic within the SPs
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Stored Procedures are better in my opinion. Here are a couple of reasons: SPs mean that you can revoke the permissions on direct table access pushing everything through SPs. This means the only actions that an application can perform are only the actions the SPs permit. SPs can do additional checking of the data. This allows the database to weed out potentially dangerous data as part of a layered security approach. e.g. The application weeds out potentially harmful data first, but if that security layer should be compromised somehow the database itself has a layers of security to prevent dangerous actions - this includes logic within the SPs
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More