Stored Procedure -- multiple inserts -- outcome to results table

[TomCarnahan]

I have a SS2K database for which I am writing a stored procedure.

The procedure takes Table A containing a list of prime keys, joins it Tables B and C. Using a combination of data from these tables, it inserts data into the Target table.

When it processes, I need to record the results as success or failure in the Results table by entering the Table A prime key with the comment of "success" or "failed" so that I can see which PK was processed.

I am guessing that I need to use transactions. If Table A contains 200 PKs and #99 is going to cause an insert error, I still want #100 thru #200 to continue processing. I refer to the PKs as s.id (source id) in the code below.

I am using the following convention:

-- Phase 1:

    BEGIN TRAN
     INSERT INTO Table_TARGET (value1, value2,value3)
     SELECT o.value1, o.value2, o.value3
     FROM Table_SOURCE s  -- source table with the PKs
     INNER JOIN Table_OTHER o
          ON s.id = o.id
     INNER JOIN Table_TARGET t
          ON s.id = t.id

     SELECT @Error = @@ERROR
     IF @Error <> 0
          BEGIN
               ROLLBACK TRAN
/*     Code to write the value of the PK that failed
to Table_Results with a "failure code". (s.id plus "Success" and the Phase number).  I am having trouble capturing the bad s.id
*/
          END
     ELSE
          BEGIN

/*     Code to write the value of the PK that was successful
to Table_Results with a "success code". (s.id plus "Success" and the Phase number)
*/
 
                COMMIT TRAN
          END

-- Phase 2:

-- *** More updates and inserts using the Source table with the list of PKs ("PK_Table ")

-- Phase 3:

-- ... etc.

GO

It could be that I am already close to the results I desire, but it is not clear to me how I would identify the PK that fails a particular stage of the process while letting the others continue. I want all PKs to process through all phases.

I shudder to think of the criticism I will receive if I mention the "C" word ("cursor") but my list of PKs is small (under 300) so I don't anticipate a intolerable performance hit if I do have to use a cursor.

Questions: Could someone give me some ideas of how to:

-- Capture the PK that causes an insert to fail to that I can write its value to the results table.

-- avoid using a cursor and / or transactions?

Thanks ahead of time for your advice.


--- Tom

 

[Crowley16]

yeah, unfortunately you can't really use t-sql that way. Each individual statement is an implicit transaction, therefore, a single insert statement either succeeds or fails as a whole.

if you want row level checking/logging then you need to use a cursor or similar looping code.

Imo, for 300 records, just use a cursor and have done with it. I think the biggest worry for people here is that cursors will be used to cater for any situation irrespective of whether they are appropriate or not...

--------------------
Procrastinate Now!

 

[markros]

This may be an ideal job for SSIS.

PluralSight Learning Library