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

    )

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

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

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:

BEGIN TRY

    BEGIN TRAN    

        INSERT INTO OutPutTesting (RecordDate, EnteredValue)

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

        VALUES (getdate(), 'Some text')

    IF @@TRANCOUNT > 0

    COMMIT TRAN

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE() AS InsertError

    ROLLBACK TRAN

END CATCH

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:

BEGIN TRY

    BEGIN TRAN    

        INSERT INTO OutPutTesting (RecordDate, EnteredValue)

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

        VALUES ('2011/01/301', 'Some text')

    IF @@TRANCOUNT > 0

    COMMIT TRAN

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE() AS InsertError

    ROLLBACK TRAN

END CATCH

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:

DECLARE @OutputResults TABLE (

      ID INT 

    , RecordDate DATETIME

    , EnteredValue VARCHAR(100))

BEGIN TRY

    BEGIN TRAN    

        INSERT INTO OutPutTesting (RecordDate, EnteredValue)

        OUTPUT INSERTED.ID, INSERTED.RecordDate, INSERTED.EnteredValue INTO @OutputResults

        VALUES (getdate(), 'Some text')

    IF @@TRANCOUNT > 0

    COMMIT TRAN

    SELECT * FROM @OutputResults

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE() AS InsertError

    ROLLBACK TRAN

END CATCH

Now if we this time attempt to insert invalid data:

DECLARE @OutputResults TABLE (

      ID INT 

    , RecordDate DATETIME

    , EnteredValue VARCHAR(100))

BEGIN TRY

    BEGIN TRAN    

        INSERT INTO OutPutTesting (RecordDate, EnteredValue)

        OUTPUT INSERTED.ID, INSERTED.RecordDate, INSERTED.EnteredValue INTO @OutputResults

        VALUES ('2011/01/301', 'Some text')

    IF @@TRANCOUNT > 0

    COMMIT TRAN

    SELECT * FROM @OutputResults

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE() AS InsertError

    ROLLBACK TRAN

END CATCH

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:

DELETE FROM OutputTesting

OUTPUT DELETED.ID, DELETED.RecordDate, DELETED.EnteredValue

WHERE ID = 3

Update can give you information on what the old and new values are for the record:

SET RecordDate = DATEADD(d, 1, GETDATE())

OUTPUT INSERTED.ID, INSERTED.RecordDate AS NewDate, DELETED.RecordDate AS OldDate

WHERE ID = 2

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:

DECLARE @InputTable TABLE (ID INT, RecordDate DATETIME, EnteredValue VARCHAR(100))

INSERT INTO @InputTable VALUES 

    (1, GETDATE(), 'Some new text'), 

    (2, GETDATE(), 'Some other text'),

    (500, GETDATE(), 'New text')

MERGE OutputTesting    as Target

USING (SELECT ID, RecordDate, EnteredValue FROM @InputTable) AS Source

ON Target.ID = Source.ID

WHEN MATCHED THEN

    UPDATE SET Target.RecordDate = Source.RecordDate, Target.EnteredValue = Source.EnteredValue

WHEN NOT MATCHED BY TARGET THEN

    INSERT (RecordDate, EnteredValue)

    VALUES (Source.RecordDate, Source.EnteredValue)

    OUTPUT $action, INSERTED.*, DELETED.*;

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?