Hello I want to have a shared warehouse whereby if I update stock in one location e.g. KFC branch A I can be able to access it in another location e.g. KFC branch b. My stored procedure for updating stock is as follows:
USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[UpdateStock] Script Date: 27-Apr-20 11:47:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateStock]
@ProductID nVarChar(4), @ChildID nVarChar(4), @LocationCode nVarChar(3), @WarehouseID as int, @LocationID int, @Quantity Decimal(10,3)
AS
BEGIN
DECLARE @StrQuery nVarChar(4000), @iRowCtr as int
SET @strQuery = 'SELECT @iRowCtr = COUNT(*)FROM Stock WHERE ProductChildID = ''' + @ProductID + @ChildID + @LocationCode + ''''
EXECUTE sp_executesql @strQuery, N'@iRowCtr int out', @iRowCtr out
/* if not Found */
IF @iRowCtr = 0
BEGIN
INSERT INTO Stock (ProductID, ChildID, LocationCode, WarehouseID, LocationID, ProductChildID, Quantity) VALUES ('' + @ProductID + '', '' + @ChildID + '', '' + @LocationCode + '', @WarehouseID, @LocationID, + '' + @ProductID + @ChildID + @LocationCode + '', + @Quantity)
END
ELSE/* if Found */
BEGIN
UPDATE Stock SET Quantity = Quantity + @Quantity WHERE ProductChildID = '' + @ProductID + @ChildID + @LocationCode + '' AND WarehouseID = @WarehouseId AND LocationID = @LocationID
END
END
