Browse DevX
Sign up for e-mail newsletters from DevX


Get Your Stored Procedures Ready for Anything-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Spell Out Your Procedure's Purpose
A stored procedure works best when doing only one thing. However, some shops are under pressure to localize the logic underlying data modifications. This centralizes code maintenance by putting the logic in one procedure instead of three or four (e.g., procedures for insert, update, and delete). Unfortunately, combining these actions makes the underlying procedure exponentially more complicated. Instead of doing only one thing, the procedure is pressed into general service, doing three or four things.

Your first line of defense in debugging this type of general-purpose stored procedure is a good header that enables you to document its purpose(s). You can over-explain things in this documentation, since how things happen should be self-evident to the reader, but the reason why an approach was taken may not be immediately evident. So the procedure's intended purpose is where you should spend your efforts when filling out your header (see Figure 1).

Click to enlarge
Figure 1: Sample Header for Your Stored Procedures

Once the header is set up and maintained, you have a running history of what is happening in the code.

Determine the Flow of Control
By branching based on the parameters sent to the procedure, you delegate control to the relevant sections to do the DML required. Assume you have a procedure that has one parameter: @Action, which could be an Insert, Update, or Delete parameter. To test the value of @Action and perform accordingly, you need a structure something like this:

IF @Action = XXXXXX BEGIN Your code here END

You should get this fleshed out by pseudo-coding your intention first and then creating the logic to accomplish what you intend. Since most errors are logic errors, getting this down in stub form will assure that the code is at least executing the flow of control correctly. This falls under the heading of "creating the algorithm first, then writing the code later." It's a good (but sometimes overlooked) practice to say what you want your code to do before you write it! The following code first stubs the flow of control:

CREATE PROCEDURE dbo.usp_TestFacilityRoster @InAction VARCHAR(15), @EntryMonth INT, @EntryYear INT, @FileType CHAR(1) AS IF @InAction ='NEWREINS' or @InAction = 'NEWREINSSNP' BEGIN --do stuff for new members END IF @InAction = 'DISENROL' OR @InAction = 'EXISTING' BEGIN --do common stuff for existing or disenrolled members IF @InAction = 'DISENROL' BEGIN --specific for disenroll ELSE IF @InAction = 'EXISTING' BEGIN --specific for existing END

Now, you've established the backbone for the procedure.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date