When SQL Output Makes Me Cry

I'm a big proponent of the SQL OUTPUT clause introduced in SQL 2008. Having the ability to output the rows of a given DML statement has a lot of great uses. You can log DML actions without the need for triggers or costly features like CDC, you can capture data moving around inside a procedure, or debug a one-off script you're running. I did however just run into a situation with makes me want to cry.

Typically with the DML procedures I work with, we like to put all the data manipulation, massaging and preparation at the start, and then at the end persist all this data to the presentation tables. While I always realized why, it became especially apparent here. If it's not clear, the benefit here is that you have all your staging done in temporary objects and if something goes wrong, you can break out and not have to worry about anything live being affected. You also avoid convoluted schemes of transactions and try catch blocks. Also you know where to look for your DML statements modifying presentation tables. Now this doesn't always work, but I think for the most part, a small amount of effort can fit this mold. One big problem comes with debugging a procedure. You need to see data flow through the procedure in order to test individual parts and see where things are going wrong. If you have a presentation table DML action early in the procedure, if you want to run it in any environment but a development environment, you likely wont have the permissions to affect the tables (and if you do, you still probably don't want to). This is easy to get around by changing the

  insert into MyPresentationTable (ColumnA, ColumnB) select ColumnA, ColumnB from #TempTable   

to something like

 select ColumnA, ColumnB into #MyTempPresentationTable from #TempTable  

Now you have a temp object which holds the temporary data you need, and you can move on with debugging. However, with the advent of the OUTPUT clause, you can now make selections from things like Merge statements, output into statements, and so forth. The case I have now is that a DML action is performed way early in the procedure, via a merge statement, and the OUTPUT of that is then used to feed a temp table used in just about every subsequent statement in the procedure. In the procedure, the $action attribute is used in conjunction with deleted.* and inserted.*. Trying to re-create the contents of a merge statement is difficult enough to do by hand, but when the situational outputs of a merge statement are then used for everything else, running this in an environment where you can't just run it and fill up objects to debug is a nightmare. I'm a big fan of the OUTPUT statement, and actually a pretty big fan of the MERGE statement as well, but please, consider how it's being used. New features are wonderful, but they don't have the time tested trial and error of longer standing methods. You have to give more thought to how you use them because dealing with them and debugging them is going to raise issues which don't typically come up with more core features.

SQL Server Templates

SQL Server Management Studio comes with a bunch of pre-built templates for scripting out procedures, functions, tables, administrative functions and more. All they are essentially are a .sql file with some special syntax in the script where values can be pasted into place. The special syntax is pretty easy to get a handle on, but at first glance can seem a little strange. Here's a sample of what you might see if you were looking at a SQL Server template (minus all the red lines I added).



There are several parts to this. Let's start with the window that's currently being displayed (in the picture, it's the window with the header "Specify Values for Template Parameters"). To get to this window, you must have a query window open in SSMS, and it wouldn't hurt to have a few tags in it (more on that later). In this query window, press ctrl+shift+M. I have no idea what the M is a mnemonic for... para(M)eters? Whatever. Once you're in this view, you'll be able to replace the special notation inside the script with values you input on this screen.

So how do you set that up? You put in tags with the notation
<[Parameter], [Type], [Value]>

[Parameter] is like the identifier for your replacements. Anywhere in your script, any time you have, say tagged in the script, all instances of that Database tag, will be replaced with the value you put in that popup window. This is the only part of the tag which is NOT optional. Think of it as your primary key.

[Type] is really just a hint to yourself about what datatype the script is expecting. Often times it's irellevant. If you're building a new Proc, you might have a tag which says , but you could just have easily put "Int" instead of "Sysname" and it wouldn't care. It's not a data type validator, it's just so that if you have a script which requires data of a certain type you know what to enter. It's worth noting that this is entirely optional. Also, if you change this data type throughout the script, it won't care. Only the first instance of the tag will be used to populate the [Type] column in the popup window.

[Value] is an optional default value in the field. In the example I showed above, more often than not, I'm looking for information_schema.columns, so I set the default of Parameter:SubType to "Columns". If i wanted to make it Routines or Tables, I just have to replace the "Columns" value with the value I want.



When you then click OK, all the values you have are propagated into those replaced tags throughout your script. It's worth mentioning that SQL treats each replacement of a value as a single undoable action, so if you've got a huge parameter list and you accidentally hit OK too soon, all the values you did (or probably didn't) submit will have to be undone one by one till you get back to your original script.



My final note on this is that if you don't have SQL 2012, or do and cant figure out how to use the snippets feature, get something like the SSMS Toolspack. It's free, and comes with a bunch of helpful tools, my favorite of which is a really simple code snippet interface. I've built little 2 - 3 character shortcuts for some of my most commonly used functions, such as the one I listed here. I type "info"+tab and it populate my query window with that code. I've got others as well, "tab"+tab for tables, "IX" for indexes and so on. When you combine this template format with the ability to call these templates up in a fraction of a second, you can really cut down extra typing time a LOT.