From time to time, you may consider rewriting your application. Maybe your application has bugs. Maybe it doesn’t scale well or the framework is no longer maintained or fully supported. Or maybe the code is just ugly. Whatever the reason, a rewrite gives you an opportunity to make improvements and lay the groundwork for future updates.
Recently, when one of our customers asked us to rewrite one of their main applications, we decided to convert all of the inline SQL statements into stored procedures. Here’s why we decided to go that route:
Stored Procedures Offer a Performance Boost
After SQL Server 2015, the majority of dynamic SQL statements can also be compiled and the resulting query plans can be cached for better performance. However, using SET NOCOUNT you can reduce the number of round trips between application and server, which provides an even greater performance increase. This is particularly noticeable when utilizing multiple statements.
Generalization Makes for Easier Maintenance
Generalization helps to reduce the linking between your data and your code and reduces maintenance overheads by centralizing. Bugs can be fixed in one place and you can alter the underlying data structure without having to update any other part. In other words, it’s much easier to write and maintain stored procedures than inline SQL statements. And, with stored procedures you have a greater control over what’s being executed against your database servers.
Improved Functionality—and Security
With stored procedures, you have easier access to program control statements, variables, temporary tables, exception handling and cursors that are not available to inline SQL statements. Stored procedures can also provide a more finely tuned stored security model. You can give execution permission on stored procedures without having to grant access rights to the underlying data tables or views. In other words, stored procedures are not only easier to manage, they also allow you to segment security.
Stored procedures can also grant protection against some forms of SQL injection attacks. They also provide input parameter checking and hide the implementation of business logic from people who have compromised an application. Of course, this will not be only protection plan against the attackers, but it can help reduce the surface area availability.
And here is a simple example on how to change the inline SQL statement into stored procedure:
FROM Product WHERE product_code = '" & prod & "'
Converted version as Stored procedures:
CREATE PROCEDURE [dbo].[Get_Product_Code_sp]
SET NOCOUNT ON;
FROM Product WHERE product_code = @product_code
Interested in optimizing your database for your Business? Anyon Consulting can help. Contact us today to learn more about our performance analytics and improvement services.