Thanks so much for the help and clear explanation. I've read that cursors should be avoided. However, this procedure is actually connected to a larger DTS package which would be too much data to post. This is the complete code for this procedure.

I am expected to add another possibility for a "replacement" product so the status can be "replacement", "available", "not available". Not sure how I will write that.

USE [OutletRetail]

GO

/****** Object: StoredProcedure [Outlet].[sp_UpdateProductStatus] Script Date: 01/16/2014 19:58:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

--Updates Product status codes to A if NULL

ALTER PROCEDURE [Outlet].[sp_UpdateProductStatus]

AS

DECLARE @strProductNo varchar(20)

DECLARE @strSellerNo1 varchar(10)

DECLARE @strSellerNo2 varchar(10)

DECLARE UpdateProductCursor CURSOR FOR

SELECT Product_No, Seller_No

FROM Outlet.tblProductMaster

WHERE Product_Status IS NULL

OPEN UpdateProductCursor

FETCH NEXT FROM UpdateProductCursor INTO @strProduct_No, @strSellerNo1

WHILE @@FETCH_STATUS = 0

BEGIN

SET @strSellerNo2 = NULL

SELECT @strSellerNo2 = Seller_No

FROM Outlet.tblProductMaster

WHERE Product_No = @strProductNo

AND Seller_No <> @strSellerNo1

AND Product_Status = 'Available'

IF (@strSellerNo2 IS NULL)

BEGIN

UPDATE Outlet.tblProductMaster

SET Product_Status = 'Available'

WHERE Product_No = @strProductNo

AND Seller_No = @strSellerNo1

END

UPDATEOutlet.tblProductMaster

SETProduct_Status = 'Not Available'

WHEREProduct_No = @strProductNo

AND Seller_No <> @strSellerNo2

AND Product_Status IS NULL

FETCH NEXT FROM UpdateProductCursor INTO @strProductNo, @strSellerNo1

END

CLOSE UpdateProductCursor

DEALLOCATE UpdateProductCursor