I had some code come through the other day from a developer, it was designed to perform an insert and then return the inserted data back to the client for display. This kind of code is probably seen very frequently out there in the real world.
Quick, Create A Table
As we’ll be playing around a little lets throw in a small table just for demonstration purposes:
Old School Insert
Here was the developer code that came through:
The first thing of note here is a complete lack of error handling in the statement. Sure we have a begin and commit tran but if there’s a problem with the insert we just don’t deal with it and a transaction will be left open (not to mention nothing being returned to the client).
The second thing to notice is how this would return data back to the client. We would actually perform a query against the table using SCOPE_IDENTITY() in our where clause. SCOPE_IDENTITY() contains the value inserted within the current session and current scope (meaning that it won’t return the value for any table that might have had an insert due to a trigger).
This kind of query was heavily used back in the 2000 days but now there is a much better way to do this. Enter…
The Output Clause
Whenever an insert, update, delete or merge is performed you can use OUTPUT to return back the impacted data.
Usage is very simple, just plop the OUTPUT right in the middle there and you are done. So taking the initial example (and for now ignoring the error aspects) we can simply rewrite it as the following:
We got back results exactly the same way as we would have in the old school fashion and did so without having to perform the extra read against the OutputTesting table.
Where Output Falls Down
Nothing’s perfect right? The thing with OUTPUT is that it will attempt to return results even if your statement fails. Let’s rewrite the query slightly and use the TRY…CATCH syntax to handle errors:
Ok, great, this worked as expected. Let’s throw a spanner in the works and pass invalid data to the insert and see what happens:
Hey, what gives? The insert failed (we have our insert error there) yet it still attempted to return results for the OUTPUT. We could end up with all kinds of problems if we tried to return that back to the client. People would freak out, it would be anarchy!
A Better Way To Handle Output
If we’re going to go ahead and use OUTPUT it’s always best to push that data into a table variable and then use that to return results back to the client if the insert is successful, otherwise we can just pass back the error:
Now if we this time attempt to insert invalid data:
All we get back is the actual insert error, so much cleaner.
We should really take the error handling a step further and use RAISERROR and pass in all the parameters, but this is at least a solid foundation and far better than what we had previously.
Output Isn’t Just For Inserts
Heck no, output is good for all you DML operations.
Delete will tell you the records you just removed:
Update can give you information on what the old and new values are for the record:
Merge has a little bonus OUTPUT feature called $action which tells you what what kind of DML action was performed as well as giving you the data:
Output Is Your Friend
Remember folks, old school has it’s place, like a Flock Of Seagulls hairstyle at an 80’s revival concert, but there are new and better things now.
Output loves you, it gives you lots of information and it won’t kill your server to give it to you. Output is putting out, are you?