question

bobbyragnar avatar image
bobbyragnar asked

Shared stock location

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
sql-server-2008stored procedureswarehouse
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.