Introduction

In this article I will describe the development cycle I use for stored procedures. I have been developing stored procedures for a little over 3 years and believe I can share some useful information about developing stored procedures. Please use what sounds good and let me know what is so far wrong that I better change or stop working with databases.

Planning

Before I start developing a stored procedure or modifying an existing one I gather as much information about the new development before typing any code. I need to know what parameters the SP must accept and which ones require a value be sent in and which ones can have a default value. I plan out in my mind how I will use each parameter to deliver the required result. I find out how I must deliver my results. Are two result sets ok or only one? Are return codes required? Do I need to send values out of the SP as output variables? Etc.

Developing

When I first started working with databases, the company I worked for only had one SQL Server. All my changes were done directly on the production database. I didn’t even realize other companies had development, testing, and/or QA environments. Where I work now we have development, testing, and production environments.

Now that I know better, I would never develop a new stored procedure nor make major changes to an existing stored procedure in production without first developing on a separate server. If having a separate database server is not an option, I would create a duplicate of the production databases on the production server and use them for development and testing. A development environment allows you to manipulate the data and structure any way you need in order to develop the best solution based on your planning for stored procedures.

Before development starts, I copy recent database backups from our production environment and restore them on our development servers. I keep the old databases until the next development cycle begins or until I run out of disk space in our development environment. I simply rename existing databases with the prefix OLD_ and then restore the production backups. Keeping the old databases gives all developers a chance to move any incomplete projects to the refreshed development databases.

I develop and modify stored procedures by scripting a drop, create, and grant permission commands from Enterprise Manager. I paste this script into Query Analyzer and do all my work from there. There are two main reasons I do this. First, I can take advantage of the search capabilities in Query Analyzer. Second, each time I run the script the create date for the SP changes. When it is time to move all changes to our testing environment, I simply sort by SP create date, mark all the SP’s that have a create date after the date the current rollout cycle started and script them all. Then I execute that script on our testing servers and save the script to modify and use for the production rollout.

I recommend you keep copies of the SP’s you develop. We backup our development databases each night and also save all SP changes at the end of the day to SourceSafe. Even though SourceSafe is not integrated with SQL Server, it is helpful to save changes there. I can view the history of the SP to see what has changed without having to ask too many questions of each developer. Instead of asking them what they changed, I simply ask them why they did what they did.

Development Testing And Analysis

Once I have created a new stored procedure or modified an existing one according to the specifications I gathered in my planning stage I execute the stored procedure a number of times to see its speed in milliseconds and its execution plan. I specifically look to see that data access steps use indexes whenever possible instead of table scans. I also focus on how quickly the number of rows being processed by each step approaches the number I get with my result set. I want as many rows as possible to be eliminated as early as possible in the execution of my queries. I usually see an increase in speed every time I reduce the number of rows passed between steps in an execution plan.

With simple stored procedures I only have to look at the execution plan a few times. With complex stored procedures, those that build queries dynamically or have multiple queries that may or may not run, I review the execution plan many times for as many important variations as I can think of. In my opinion, the possibility to run every variation of a stored procedure diminishes as the number of parameters it uses increases. At some point it becomes impossible to test every variation. In these cases I test what happens when all parameters have values, each parameter singly, and then a few combinations of parameters.

I always test dynamic stored procedure non-numeric parameters with a single quote to make sure it will not cause an error. Any error that occurs during this test indicates the stored procedure is vulnerable to an injection attack. Since I don’t want anyone running code other than what is built in the stored procedure I block this by replacing every single quote with two single quotes. This ensures all characters entered by a user remains in the query as part of a search criteria and will never be run as a separate query. This technique is only needed when you build a query in a variable for later execution.

Intensive Testing

Once I am satisfied with the stored procedure I stop working with it until it is time to move it to our testing environment. Before executing the rollout script in our testing environment I copy recent production backups to our testing servers and restore them. Again I rename existing databases with the prefix OLD_ and then restore the production databases. This way our intensive testing is done using recent data and ensures the indexes, tables, and other objects are identical to that of our current production environment.

The main difference between our testing environment and our development environment is that people other than myself will now test the SP I developed. Unfortunately, testing is usually only done by developers in our technology department. Testing would be better if employees not in the tech department tested the SP. In my opinion people that have not helped with the development of new features in SP’s are more likely to test the limits and find more bugs than someone who has worked on the project.

I instruct our other SQL Programmers to test everything we developed with the intent to cause an error. Once an error is generated it is sent to the entire SQL team and whoever worked on it will then fix the bug in our development environment. I move the revised SP to our testing environment so that more testing can be done on it. After moving it I add it to the rollout script. Then the SP is tested again to ensure the bug was removed. We test for one to two weeks in our testing environment before the production rollout.

We have two identical testing servers. All intensive testing is done on one server until the last one or two days of testing. At this point most of the bugs have been found and the changes applied to the rollout script. I then run the rollout script on the other testing server and all testing moves to that server. This is how I verify that we have all changes in our rollout script prior to rollout to production.

Rollout

I execute all rollout scripts on the appropriate production servers. Once done, all developers involved in the rollout begin testing to ensure all permissions and changes are set properly. We spend 2 to 4 hours during the night testing. Once everything seems to be working we call the rollout complete and go home.

Conclusions

It is very important to plan out each step of your development cycle to minimize surprises and ensure valid testing. Keep previous versions of stored procedures for reference purposes. Never develop SP’s on a production server unless you have no choice. When forced to develop in a production environment never develop on a production SP.