IF not EXISTS ( SELECT id FROM @Sale_tb WHERE OldSaleCount + 1 = NewSaleCount AND id = @ProductId ) BEGIN SET @ROut = 10132 --库存不足 ROLLBACK TRAN RETURN END 这一段少了Begin...End
[SQL]declare @rout int exec [Interface].[ProductSale_Add] productid,@rout out select @rout[Err] 42000 - [SQL Server]从数据类型 nvarchar 转换为 int 时出错。感谢补白,另外从错误信息上看,你应该是有NVARCHAR转化成INT时出错,有没有可能,SaleCount或TotalSaleCount 不是整型
42000 - [SQL Server]必须声明标量变量 "@ROut"。
42000 - [SQL Server]必须声明标量变量 "@ROut"。查询时间: 0.042s
受影响的行: 0
ALTER PROCEDURE [Interface].[ProductSale_Add] --生成新一期商品
@ProductId INT,--商品ID
@ROut INT OUTPUT --输出执行结果
AS
begin
set @ROut=1
DECLARE
@ProductId INT,--商品ID
@ROut INT OUTPUT AS--输出执行结果
begin
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 <= TotalSaleCountIF not EXISTS (
SELECT
id
FROM
@Sale_tb
WHERE
OldSaleCount + 1 = NewSaleCount
AND id = @ProductId
)
SET @ROut = 10132 --库存不足
ROLLBACK TRAN
RETURNDECLARE
@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
end
这个应该也过不去吧
你是怎么执行的?declare @rout int
exec [Interface].[ProductSale_Add] productid,@rout out
select @rout
exec [Interface].[ProductSale_Add] productid,@rout out
select @rout[Err] 42000 - [SQL Server]从数据类型 nvarchar 转换为 int 时出错。
sql server好像没这种语法吧,期待帮我补白下
sql server好像没这种语法吧,期待帮我补白下http://wenku.baidu.com/view/0ed9a321ccbff121dd368381.html?qq-pf-to=pcqq.group
看起来创建语句没错,请给出完整的调用语句。
SELECT
id
FROM
@Sale_tb
WHERE
OldSaleCount + 1 = NewSaleCount
AND id = @ProductId
)
BEGIN
SET @ROut = 10132 --库存不足
ROLLBACK TRAN
RETURN
END
这一段少了Begin...End
exec [Interface].[ProductSale_Add] productid,@rout out
select @rout[Err] 42000 - [SQL Server]从数据类型 nvarchar 转换为 int 时出错。感谢补白,另外从错误信息上看,你应该是有NVARCHAR转化成INT时出错,有没有可能,SaleCount或TotalSaleCount 不是整型