OUTPUT Clause Basics (For Inserts and Deletes)

  • Comments posted to this topic are about the item OUTPUT Clause Basics (For Inserts and Deletes)

  • OUTPUT has an impact upon @@IDENTITY and SCOPE_IDENTITY() that would be worth knowing about. I learned it the hard way - The Joys of @@ODENTITY, SCOPE_IDENTITY() and IDENT_CURRENT

    Steve Hall
    Linkedin
    Blog Site

  • Thanks Steve - wasn't aware it did this..

  • Very useful article... although you forgot to included "Spirit Level Bubbles" in your list 🙂
    I'd be interested to know when you would use an output table to do this sort of change tracking and when you would use a trigger? I can see for example that it's more transparent to do it in the same procedure that is making the changes, but a trigger would catch all changes not just ones from this procedure. Anyway, be interested to hear why/when it would be better to use output as opposed to triggers?
    Thanks
    Post reply

  • Thanks for your feedback - great question too!  I completely agree with you that triggers are a more complete solution, but this is such a handy technique I figured it worth sharing.  I find it really handy when debugging inserts and deletes (so I can actually get a feel for what's happening and quickly). I'm looking at writing a couple more articles around this subject so will likely add some discussion around the advantages / disadvantages.
    Thanks,
    Mat

  • there was error in the datatype size, please update it from Varchar(10) to varchar(100)

  • Of course, I hadn't thought about using it for debugging and checking purposes - was purely thinking about tracking (single track mind I guess)! I tend to write all my inserts/updates/deletes as selects first to ensure I'm getting the right rows and then convert them to deletes/inserts/updates at the end. But I guess using "output" could be more effective for debugging code with multiple steps. Definitely adding this to my tool belt. Thanks again.
  • We use OUTPUT for capturing new key values added. When I started there was code like this:

    CREATE PROC dbo.TheProc
    @InputPrams datatypes
    AS
    declare @NewKey int;
    SELECT @NewKey = MAX(TheKey) + 1 FROM dbo.TheTable;
    INSERT dbo.TheTable (TheKey, ...)
    VALUES (@NewKey, @InputParams, ...);
    ...
    RETURN (@NewKey);

    But that is not thread safe, so we now do it this way:

    CREATE PROC dbo.TheProc
    @InputParams datatypes,
    @NewKey int OUTPUT
    AS
    declare @tKey table (TheKey int);
    INSERT dbo.TheTable (TheKey, ...)
    OUTPUT inserted.TheKey
    INTO @tKey(TheKey)
    SELECT MAX(TheKey) + 1, @InputParams, ...
    FROM dbo.TheTable;
    SELECT @NewKey = TheKey FROM @tKey;
    RETURN 0;

    We do a similar thing when we use key columns with the IDENTITY property.

    The original code also made the mistake of returning data instead of status in the return value.

    • This reply was modified 10 months, 4 weeks ago by  JediSQL. Reason: Missing "MAX" in first code block

    Sincerely,
    Daniel

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply