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

Question

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

Old Hand

Points: 377

December 17, 2024 at 6:16 pm

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 19 hours, 51 minutes ago by tim8w. Reason: Title got trunked

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

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

Chrissy321 SSCoach Points: 15766 More actions · Answer 1

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