ALTER PROCEDURE [Interface].[ProductSale_Add] --生成新一期商品
@ProductId INT,--商品ID
@ROut INT OUTPUT AS--输出执行结果
set @ROut=1
DECLARE
@ProductSale_CurTime DATETIME = GETDATE()
DECLARE
@Sale_tb TABLE --临时表
(
Id INT NOT NULL,
OldSaleCount INT DEFAULT (0),--原始商品期次
NewSaleCount INT DEFAULT (0)--新商品期次
)
BEGIN TRAN
UPDATE dbo.Product
SET SaleCount = SaleCount + 1 OUTPUT INSERTED.Id,
ISNULL(DELETED.SaleCount, 0),
ISNULL(INSERTED.SaleCount, 0)
INTO @Sale_tb
WHERE id = @ProductId
AND SaleCount + 1 <= TotalSaleCount
IF not EXISTS (
SELECT
id
FROM
@Sale_tb
WHERE
OldSaleCount + 1 = NewSaleCount
AND id = @ProductId
)
SET @ROut = 10132 --库存不足
ROLLBACK TRAN
RETURN
DECLARE
@ProductOrderDetail_Id INT = 0
INSERT INTO [Users].[ProductSale] (
[SaleId],
[ClassId],
[ProductId],
[Title],
[LargeImage],
[SmallImage],
[ThumbImage],
[ShortSummary],
[Description],
[Price],
[Fee],
[PerFee],
[OrderCount],
[UserName],
[UserId],
[NickName],
[PublishResult],
[PublishTime],
[CompleteTime],
[ExpressNumber],
[ExpressName],
[Receiver],
[LastOrderTime],
[Hits],
[Timeout],
[Range],
[SortId],
[Status],
[Creator],
[UpdateTime],
[AddTime],
[IsLimitTime],
[LimitEndTime],
[LimitTimeout]
)
SELECT
b.NewSaleCount,
[ClassId],
@ProductId,
[Title],
[LargeImage],
[SmallImage],
[ThumbImage],
[ShortSummary],
[Description],
[Price],
[Fee],
[PerFee],
0,
'',
0,
'',
'',
@ProductSale_CurTime,
@ProductSale_CurTime,
'',
'',
'',
@ProductSale_CurTime,
0,
[Timeout],
[Range],
0,
1,
'',
@ProductSale_CurTime,
@ProductSale_CurTime,
[IsLimitTime],
case when ISNULL([IsLimitTime], 0)>0
then DATEADD(SECOND,[LimitTimeout],@ProductSale_CurTime) end,
[LimitTimeout]
FROM dbo.Product a INNER JOIN @Sale_tb b
ON a.id = b.id
AND b.NewSaleCount <= a.TotalSaleCount
WHERE
a.id = @ProductId
SET @ProductOrderDetail_Id = SCOPE_IDENTITY()
IF ISNULL(@ProductOrderDetail_Id, 0) = 0 --生成订购记录失败
BEGIN
SET @ROut = 10150
ROLLBACK TRAN
RETURN
END
COMMIT tran
------解决思路----------------------
目测你的存储过程没有问题啊
你是怎么执行的?
declare @rout int
exec [Interface].[ProductSale_Add] productid,@rout out
select @rout