First Stored Procedure

Today, I created my first stored procedure. I'm have mixed emotions about it. I was never a fan of stored procedures throughout my career, I didn't like the idea that business logic will be housed in the database layer. Here are some of my reasons for that:

a). It is hard to debug SP code. I was working with a requirement wherein I need to add an new field to be updated in a table. This task required me to make some modifications to a backend call which calls an SP to update data in the database. Initially, I thought I would just need to add a new field in the SP and update its mapping in the backend. Alas, it didn't work! I encountered a casting error with the SP's parameter against the dataset. I investigated the cause of the error using a variety of methods but I just get the same error message which does not help at all. This was so frustrating since I just wanted to update a single field and how hard could that be?!?! I really, really wanted to create a new implementation on updating the field but I couldn't justify why I need to do so. After two days of investigation, the team decided to create new method to update the field but sad to say it still uses an SP.

b). Hard to maintain in different environments (PT, QA and Prod). It is a pain that you need to update all instances of the SP in different environments every time you have changes. Unlike if you do the business logic in code, you just need to update it in a single place and deploy to different environments. With this, it is less likely you will break something in the other environments compared  to doing an update in all affected environments.

c). Makes the application slower. This is because of the additional layer it needs to go through before the result is obtained by the application. Usually, this is evident when the result from the database layer processing needs to be processed in the application's backend.

However, in spite of these, I guess I'm glad that I created a new SP since it signified that the sprint I was working on for almost a month is almost over! I can't wait to work on something different.