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
December 17, 2024 at 7:59 pm
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
December 18, 2024 at 4:15 pm
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