Need to return a value that indicates that a record has been added, but not when a record is modified

Transact-SQL
1

I have a stored procedure that either updates an existing record or adds one if it does not exist. I need to return a value indicating that an INSERT has happened.

USE [FPY]
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
2

You have two choices:
(1) you can use the return code value, for example, 0=update, 1=insert
(2) you can use an OUTPUT parameter, for example:

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
           ,@is_insert bit = 0 OUTPUT)
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)
BEGIN
SET @is_insert = 1
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 /*IF*/
ELSE
SET @is_insert = 0
END /*PROC*/
4

Changed the calling code and SP to try and get more info and I can verify that the SP is not returning any valid value.

Here's the calling code:

Int32 iInserted = 3;
iInserted = Convert.ToInt32(myCmd.ExecuteScalar());

Here is the SP:

USE [FPY]
GO
/****** Object:  StoredProcedure [dbo].[insupd_tblMRBHistory]    Script Date: 2024-12-19 7:25:36 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)
	BEGIN
		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)
		RETURN 1
	END
	ELSE
	BEGIN
		RETURN 2
	END
END

As you can see, I should be getting either a 1 or 2 returned. I ALWAYS get a zero, which I assume that is what C# is converting the null into.

5

Does using an int work?

int retVal = (int)myCmd.ExecuteScalar();
Console.WriteLine("Returned Number: " + retVal);
6

That's what I did above with the Convert.ToInt32. If you don't do that, then you still get a null.

7

Probably best to post the rest of your ado.net on a c# site.

8

Use a Merge. You can output your choice of inserted, updated or deleted.
Can I post a URL from another site here? Have a great example...

9

MERGE is badly implementated in SQL Server. eg:

What To Avoid If You Want To Use MERGE | Michael J. Swart

10

If I change RETURN 1 and RETURN 2 to SELECT 1 and SELECT 2, it works. Why does RETURN not work?

11

Did you add the parameter?