OUTPUT Clause Basics (For Inserts and Deletes)

Question

OUTPUT Clause Basics (For Inserts and Deletes)

Mat Richardson

SSC-Addicted

Points: 401
More actions
May 2, 2017 at 12:00 am

#321194

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

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

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

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

Mat Richardson SSC-Addicted Points: 401 More actions · Answer 2

Mat Richardson

SSC-Addicted

Points: 401

May 2, 2017 at 2:06 am

#1940650

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

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 3

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

Mat Richardson SSC-Addicted Points: 401 More actions · Answer 4

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

dileep_Vanka SSC Rookie Points: 39 More actions · Answer 5

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

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 6

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.

JediSQL SSCrazy Points: 2118 More actions · Answer 7

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 4 years, 10 months ago by JediSQL. Reason: Missing "MAX" in first code block

Sincerely,
Daniel