CREATE PROCEDURE [dbo].[SP_Price_ReceiveID](
@ItemID varchar(15),
@Qunitity int,
@i int = 0, @StockStory varchar(100) =''
) AS
WHILE @i< (SELECT COUNT(dbo.tbItemReceive.ItemReceiveID) AS Expr1
FROM dbo.tbItemReceive INNER JOIN
dbo.tbItemReceiveItem ON dbo.tbItemReceive.ItemReceiveID = dbo.tbItemReceiveItem.ItemReceiveID
WHERE (dbo.tbItemReceiveItem.QuantityReveive - dbo.tbItemReceiveItem.QuantityDistribute > 0) AND (dbo.tbItemReceiveItem.ItemID = @ItemID))
BEGIN
set @qunitity = (@qunitity-(SELECT TOP 1 dbo.tbItemReceiveItem.QuantityReveive - dbo.tbItemReceiveItem.QuantityDistribute AS countItme
FROM dbo.tbItemReceive INNER JOIN
dbo.tbItemReceiveItem ON dbo.tbItemReceive.ItemReceiveID = dbo.tbItemReceiveItem.ItemReceiveID
WHERE dbo.tbItemReceiveItem.ItemReceiveID NOT IN(@StockStory) and (dbo.tbItemReceiveItem.QuantityReveive - dbo.tbItemReceiveItem.QuantityDistribute > 0) AND (dbo.tbItemReceiveItem.ItemID = @ItemID)))
set @StockStory =
@StockStory +((SELECT TOP 1 CONVERT(varchar , dbo.tbItemReceiveItem.ItemReceiveID)+','
FROM dbo.tbItemReceive INNER JOIN
dbo.tbItemReceiveItem ON dbo.tbItemReceive.ItemReceiveID = dbo.tbItemReceiveItem.ItemReceiveID
WHERE dbo.tbItemReceiveItem.ItemReceiveID NOT IN(@StockStory) and (dbo.tbItemReceiveItem.QuantityReveive - dbo.tbItemReceiveItem.QuantityDistribute > 0) AND (dbo.tbItemReceiveItem.ItemID = @ItemID)))
if @qunitity >0
begin
set @i = @i+1
end
else
begin
BREAK
end
END
SELECT *
FROM dbo.tbItemReceive INNER JOIN
dbo.tbItemReceiveItem ON dbo.tbItemReceive.ItemReceiveID = dbo.tbItemReceiveItem.ItemReceiveID
WHERE dbo.tbItemReceiveItem.ItemReceiveID IN(@StockStory)
GO
แล้ว Error มันขึ้นว่า
Syntax error converting the varchar value '2,' to a column of data type int.
จะมีทางแก้ยังงัยครับช่วยหน่อยนะครับขอบคุณครับผม
Load balance : Server 04
