How can I tell which action happened from the UPDATE or INSERT in my SP?

  • I have the following Stored Procedure that attempts to UPDATE a table, and if there is no corresponding record INSERTS one. How do I indicate which case happened?

    GO

    /****** Object:  StoredProcedure [dbo].[insupd_tblMRBHistory]    Script Date: 2024-12-17 9:27:52 AM ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[insupd_tblMRBHistory]
    (@BusinessUnit char(30)
    ,@PartNum char(25)
    ,@Description varchar(50)
    ,@Category char(4)
    ,@LotNum char(25)
    ,@LocationID int
    ,@StockMRB int
    ,@ExtendedCost decimal(7,2)
    ,@TransDate datetime
    ,@MRBDays int
    ,@Closed bit)
    AS
    BEGIN
    UPDATE [dbo].[tblMRBHistory]
    SET        [BusinessUnit] = @BusinessUnit,
    [PartNum] = @PartNum,
    [Description] = @Description,
    [Category] = @Category,
    [LotNum] = @LotNum,
    [LocationID] = @LocationID,
    [StockMRB] = @StockMRB,
    [ExtendedCost] = @ExtendedCost,
    [MRBDays] = @MRBDays,
    [Closed] = @Closed
    WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum AND [LocationID] = @LocationID

    IF (@@ROWCOUNT = 0)
    INSERT INTO [dbo].[tblMRBHistory]
    ([BusinessUnit],
    [PartNum],
    [Description],
    [Category],
    [LotNum],
    [LocationID],
    [StockMRB],
    [ExtendedCost],
    [TransDate],
    [MRBDays],
    [Closed])
    VALUES
    (@BusinessUnit,
    @PartNum,
    @Description,
    @Category,
    @LotNum,
    @LocationID,
    @StockMRB,
    @ExtendedCost,
    @TransDate,
    @MRBDays,
    @Closed)
    END

    • This topic was modified 2 months ago by  tim8w. Reason: Title got trunked
  • Sounds like you could declare a stored procedure output parameter and set it in your IF statement.

    https://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/

    Or if you need more flexibility you could use the OUTPUT clause.

    https://www.sqlservercentral.com/articles/the-output-clause-for-insert-and-delete-statements

  • You can use Extended Events to capture the statements executed. If I was doing this one, I would capture rpc_staring, sp_statement_completed and rpc_completed. All three filtered to the object id for the procedure in question. It'll tell you exactly what happens.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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