Are You Putting Out?–The SQL OUTPUT Clause

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:

CREATE TABLE OutputTesting
    (
      ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , RecordDate DATETIME NOT NULL 
    , EnteredValue VARCHAR(100) NOT NULL
    )

CREATE TABLE OutputTesting

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

, RecordDate DATETIME NOT NULL

, EnteredValue VARCHAR(100) NOT NULL

)

Old School Insert

Here was the developer code that came through:

BEGIN TRAN
        INSERT INTO OutPutTesting (RecordDate, EnteredValue)
        VALUES (getdate(), 'Some other text')      
SELECT ID, RecordDate, EnteredValue FROM OutputTesting WHERE ID = SCOPE_IDENTITY()
COMMIT TRAN

BEGIN TRAN

INSERT INTO OutPutTesting (RecordDate, EnteredValue)

VALUES (getdate(), 'Some other text')

SELECT ID, RecordDate, EnteredValue FROM OutputTesting WHERE ID = SCOPE_IDENTITY()

COMMIT TRAN

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:

BEGIN TRAN
        INSERT INTO OutPutTesting (RecordDate, EnteredValue)
        OUTPUT INSERTED.ID, INSERTED.RecordDate, INSERTED.EnteredValue 
        VALUES (getdate(), 'Some other text')
COMMIT TRAN

BEGIN TRAN

INSERT INTO OutPutTesting (RecordDate, EnteredValue)

OUTPUT INSERTED.ID, INSERTED.RecordDate, INSERTED.EnteredValue

VALUES (getdate(), 'Some other text')

COMMIT TRAN

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:

Continue reading on SirSQL.net.