Much of the routine SQL code you write for applications is redundant, or should be.  Your creative contribution is, in percentage terms, quite minor. The simple code to call stored procedures, do simple SELECTS from tables or views, INSERTS, UPDATES, use Table-valued functions and so on are almost entirely determined by the metadata that is accessible in the system views.  It just needs touching up here and there for the occasion is it used.  Why then, spend so much typing out stuff when it can be generated automatically?

In this article, I’ll be showing you how to produce properly documented,  run-of-the-mill code extremely quickly with very little frustrating effort, using system views.  If you are a SQL Server developer who hates repetitive typing, then read on.

Beautifully-documented too

Fortunately there are already tools for coming up with simple SELECT statements (e.g. SQL Prompt and SSMS Tools: even SSMS does it now), but the ones I’ve seen assume that you have been  too idle to properly document the database you’ve created  by using extended properties. There is some excuse for this. SQL Server Management studio makes it ridiculously tiresome to do so, and impossible in places.  This is one of the instances where I’m happy to be dogmatic: I never ever allow a database through a code-review unless every database object has at least a summary explanation of what on earth it does or why it is there.  There is even a handy utility, SQLTAC, to check such things, and prompt you to add the documentation, or ‘domain knowledge’ as Steve refers to it.  (Yes, SQL Doc has a nice interface for filling in extended properties.) 

Here is a TSQL expression (SQL Server 2005 or above)  that will at least tell you what code in a database is not properly documented, when you come to look at a database.

Some programmers will write automated ways of filling in the extended properties to try to defeat this. Even in AdventureWorks, you will find crime scenes like this, where a programmer wanted to get home early.

WWhich gives the following result on Adventureworks…

Generating Stored Procedure calls automatically

The payback to documenting your code is immediate, If you use the  SQL  routines that  I provide in the way  I’m going to show you, you can generate SQL for your database that is immaculately documented. In my last article, I showed you how to  create fully-commented  SELECT statements for all your base tables and views. This is handy, but wouldn’t you find something for calling stored procedures useful?  If you answered ‘no’ to that question, then you need to get more familiar with the templates in SSMS.  All you need to do is to create template files that allow you to fill in the parameters from a form within SSMS. If you have a database that has  a large number of stored procedures,  you can merely generate the EXECUTE  statements on the fly, as or when you need them.  In a later article, I’ll show you ways to  generate the templates and put them in your template directory so that they appear in the template browser. From then, it is just a matter of dragging and dropping. Yes, SSMS should be like that, but it isn’t.

Here’s the SQL Statement. Brace yourself, it is a bit scary, but I’m not going to insist that you understand it, just the process.

The result of this, when executed against AdventureWorks, looks like this.

But we’ll pick just one item  from the text version of the result pane.

Looks a bit odd, I grant you, because this is a template rather than an executable TSQL Expression. Hit Cntl Shift M

This appears

Yes, fill it in (I’ve just done so in the screen-grab) and bang the button.  The parameters have been filled in

It would have been so easy for SSMS to do this. Be warned though, the current version does not do output variables. The code was getting a bit long, and I won’t do it unless you pester me into believing that it is useful. (I add them by hand)

Automating The Simple Update Statement

 

There is an extra complication here because you will want to update a row based on a value of the primary key more often than not, and the primary key, if it exists, can involve more than one row. Here, you will get something that looks like this, using Adventureworks’s ProductInventory as an example.

This time, I’ve given you too much. You’ll  want to give it a haircut. I’m tempted not to show key columns as it is rare to want to alter those but the delete key is the easiest one on the keyboard to use. With these queries, I’ve used the principle that it is easier to pare stuff back than to have to type code in. So, we take out the first two lines and hit Cntl Shift M

You’ll notice one or two restrictions. You might want to put GetDate() in the ModifiedDate field , but this will require a small amount of editing. Still, faster than typing all those comments by hand. I’ve also forgotten to leave out computed columns. You’ll find it easy to alter the code.

Generating Code Templates For Table-Valued Functions

So next, we do some minor alterations to product the code for a Table-Valued Function. This, together with the Stored Procedure is going to be the classic component of an interface. As the SQL is pretty similar, I won’t publish it, in the article body but you can download the code from the speech-bubble.

In order to test it, i actually had to add the documentation into AdventureWorks for the columns passed back. Howver, it gives you an idea of the payback through doing it, if your function is called several times. This is particularly true of the parameters: this level of documentation makes it very easy to follow and check through code

Automatically generating simple INSERT…VALES statements

The INSERT  statement is somewhat of an anticlimax after this behemoth. I Won’t show it here but I’ll let you download it at the bottom of the article. The way the template is used is identical so I won’t show that either. It produces some nice code like this

(apologies for causing chaos in Adventureworks’ business by my guess at legit values)

Conclusions

With the editor nervously signaling to me that I have outrun my 2000 words, I’d like to round up by saying that the combination of the System views, the Varchar(MAX) datatype and the rather suspect technique of using FOR XML PATH(''), one can automate quite a lot of  routine database work.  If you study the code, you will see that I’m just reusing the same sections of code over and over again but in different ways to suit the syntax, and the object.  You’ll also notice that one doesn’t really stray from a small number of System Views.

Although these templates can be used with the techniques I showed you at the start of the series, using the SELECT statement as an example, there is more one can do to make it slick. The next stage is to be able to generate automatically all the application’s template code automatically for every workstation, so that one can have them directly draggable-n-droppable from the Template Explorer whenever you need to do some routine CRUD. I’ll admit that this is more likely with views, Stored Procedures and Table-valued functions, but then I though I ought to include table/view work as well.  This would suggest that every DBA’s dream of having a defined independent interface between the application and the database base-tables could be made a much more realistic alternative with the basic operations made much easier for the application developer via templates.

Putting automatically-generated templates into SSMS requires a workstation script, in PowerShell or whatever. We’ll be using whatever, and hope to show you more soon.