Reclaiming Stored Procedure Code

One of the largest problems people have with keeping stored procedures in source control is not a disagreement that they should be but the change to the process in which they are accustomed to. Normally with code, you create a physical file which is then piped to the compiler to output an assembly. With Microsoft’s SQL Server Management (SSMS) Studio the process of creating a file and then running it has changed this, because the database stores the SQL code for procedures in the database negating the need for a physical file. This is a convenient shortcut but makes it difficult to keep SQL source in sync, because inevitably people forget to create the file from Management Studio when they’re done. SSMS has a menu item for generating a file, but the creation options are limiting depending on the need, and it adds additional code around the procedure which most of the time isn’t necessary. Not to mention generating one file is simple enough, but having to do it for hundreds of changed procedures is tedious and time consuming.

Script Procedure

Fortunately, there is a convenient fix for this. Since MSSQL Server stores the code in the database, you can easily retrieve the code and save it yourself. Microsoft created the INFORMATION_SCHEMA.ROUTINES view which returns information such as it’s name, type, schema, and most importantly, the code. Using this approach you can also format the code the way you want when saving it to a file. I always make the scripts so they don’t fail so I prepend the necessary header code and change the create to an alter command before saving it.

File on Github at: https://github.com/kemiller2002/StructuredSight/blob/master/GettingProcedures/GetProcedures.ps1

Mockups – Now with moving pictures

Wireframing applications are great for creating quick visuals of what a screen or series of screens may look like. mockup The quality of the picture compared to the time required to create it is priceless, because no one spends too much time creating a visual display which will ultimately change and be discarded after the team creates the final product.

There are a lot of tools which create quick displays of user interfaces. My personal favorite is from Balsamiq . It’s easy to create mockups of different screens Button Link and Balsamiq has a way of linking mockups together showing the progression. Conveniently there is a presentation mode should someone need to demonstrate it to a group.

The issue with this is that everyone in involved in the decision process still needs a license and know how to use the application or people must meet and those responsible for the design must explain it. Even if everyone is in the same office this still can be a cumbersome task, and furthermore making decisions based on something seen for the first time doesn’t allow one to reflect on what’s presented.

Combing the linked screen ability with video capture software allows you to quickly create videos showing the progression of an application. Although I prefer the SnagIt interface a little better, a program like CamStudio is free and does the job. Now you can email the video or post it to an intranet, and everyone can view it at their convenience and respond at their leisure.