CREATE PROCEDURE [dbo].[UP_Products_AddJewelry]
@ProductId int output,
@Category1 int,
@Category2 int,
@Category3 int,
@ProductSN varchar(80),
@ProductName varchar(100),
@PurchasePrice decimal(18,0),
@MarketPrice decimal(19,2),
@PreferentialPrice decimal(19,2),
@VipPrice decimal(18,2),
@Pic1 varchar(100),
@Pic2 varchar(100),
@Pic3 varchar(50),
@Pic4 varchar(100),@LongDescription ntext,
@Stocks int,
@Commend bit,
@Hot bit,
@HotSales bit,
@Agio bit,
@LowPrice bit,
@Hidden bit,
@Hit int,
@Sort int,
@AddDate smalldatetime,
@LastUpdatedDate smalldatetime,
@RelationshipProducts varchar(1000),
@FileUrl varchar(50),
@TempletId int,@Pattern nvarchar(50),
@Texture nvarchar(50),
@Brand nvarchar(50),
@Style nvarchar(50),
@Shape nvarchar(50),
@Inlay nvarchar(50) AS
BEGIN TRY BEGIN TRAN --开始事务
INSERT INTO Products(
[Category1],[Category2],[Category3],[ProductSN],[ProductName],[PurchasePrice],[MarketPrice],[PreferentialPrice],[VipPrice],[Pic1],[Pic2],[Pic3],[Pic4],[LongDescription],[Stocks],[Commend],[Hot],[HotSales],[Agio],[LowPrice],[Hidden],[Hit],[Sort],[AddDate],[LastUpdatedDate],[RelationshipProducts],[FileUrl],[TempletId]
)VALUES(
@Category1,@Category2,@Category3,@ProductSN,@ProductName,@PurchasePrice,@MarketPrice,@PreferentialPrice,@VipPrice,@Pic1,@Pic2,@Pic3,@Pic4,@LongDescription,@Stocks,@Commend,@Hot,@HotSales,@Agio,@LowPrice,@Hidden,@Hit,@Sort,@AddDate,@LastUpdatedDate,@RelationshipProducts,@FileUrl,@TempletId
)
SET @ProductId = @@IDENTITY
INSERT INTO Products_Property([ProductId],[Pattern],[Texture],[Brand],[Style],[Shape],[Inlay])VALUES(@ProductId,@Pattern,@Texture,@Brand,@Style,@Shape,@Inlay) COMMIT TRAN;
Return @ProductId ---返回ProductIdEND TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Return -1
END CATCH;
---------------------------
正确没有问题,如果将Products_Property表名改一下(为了演示错误)则提示:在 EXECUTE后的事务计数指示缺少了COMMIT和ROLLBACK TRANSACTION 语句。上一计数=1,当前计数=0。
请教这是什么错误?
@ProductId int output,
@Category1 int,
@Category2 int,
@Category3 int,
@ProductSN varchar(80),
@ProductName varchar(100),
@PurchasePrice decimal(18,0),
@MarketPrice decimal(19,2),
@PreferentialPrice decimal(19,2),
@VipPrice decimal(18,2),
@Pic1 varchar(100),
@Pic2 varchar(100),
@Pic3 varchar(50),
@Pic4 varchar(100),@LongDescription ntext,
@Stocks int,
@Commend bit,
@Hot bit,
@HotSales bit,
@Agio bit,
@LowPrice bit,
@Hidden bit,
@Hit int,
@Sort int,
@AddDate smalldatetime,
@LastUpdatedDate smalldatetime,
@RelationshipProducts varchar(1000),
@FileUrl varchar(50),
@TempletId int,@Pattern nvarchar(50),
@Texture nvarchar(50),
@Brand nvarchar(50),
@Style nvarchar(50),
@Shape nvarchar(50),
@Inlay nvarchar(50) AS
BEGIN TRY BEGIN TRAN --开始事务
INSERT INTO Products(
[Category1],[Category2],[Category3],[ProductSN],[ProductName],[PurchasePrice],[MarketPrice],[PreferentialPrice],[VipPrice],[Pic1],[Pic2],[Pic3],[Pic4],[LongDescription],[Stocks],[Commend],[Hot],[HotSales],[Agio],[LowPrice],[Hidden],[Hit],[Sort],[AddDate],[LastUpdatedDate],[RelationshipProducts],[FileUrl],[TempletId]
)VALUES(
@Category1,@Category2,@Category3,@ProductSN,@ProductName,@PurchasePrice,@MarketPrice,@PreferentialPrice,@VipPrice,@Pic1,@Pic2,@Pic3,@Pic4,@LongDescription,@Stocks,@Commend,@Hot,@HotSales,@Agio,@LowPrice,@Hidden,@Hit,@Sort,@AddDate,@LastUpdatedDate,@RelationshipProducts,@FileUrl,@TempletId
)
SET @ProductId = @@IDENTITY
INSERT INTO Products_Property([ProductId],[Pattern],[Texture],[Brand],[Style],[Shape],[Inlay])VALUES(@ProductId,@Pattern,@Texture,@Brand,@Style,@Shape,@Inlay) COMMIT TRAN;
Return @ProductId ---返回ProductIdEND TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Return -1
END CATCH;
---------------------------
正确没有问题,如果将Products_Property表名改一下(为了演示错误)则提示:在 EXECUTE后的事务计数指示缺少了COMMIT和ROLLBACK TRANSACTION 语句。上一计数=1,当前计数=0。
请教这是什么错误?
if @@trancount>1
rollback tran
end catch
if @@trancount >0
ROLLBACK TRANSACTION
Return -1
END CATCH;
会不会自动回滚了?
我再找找帮助哦
在开始加这句看看还报错不
AS
set xact_abort on
BEGIN TRY BEGIN TRAN --开始事务
你的表找不到,属于数据库连接错误.
begin catch
SELECT ERROR_LINE() AS ErrorLine;
--rollback transaction
--return -1
end catch;你就能知道并未进入catch块了.
AS
BEGIN TRAN --开始事务
INSERT INTO Products(
[Category1],[Category2],[Category3],[ProductSN],[ProductName],[PurchasePrice],[MarketPrice],[PreferentialPrice],[VipPrice],[Pic1],[Pic2],[Pic3],[Pic4],[LongDescription],[Stocks],[Commend],[Hot],[HotSales],[Agio],[LowPrice],[Hidden],[Hit],[Sort],[AddDate],[LastUpdatedDate],[RelationshipProducts],[FileUrl],[TempletId]
)VALUES(
@Category1,@Category2,@Category3,@ProductSN,@ProductName,@PurchasePrice,@MarketPrice,@PreferentialPrice,@VipPrice,@Pic1,@Pic2,@Pic3,@Pic4,@LongDescription,@Stocks,@Commend,@Hot,@HotSales,@Agio,@LowPrice,@Hidden,@Hit,@Sort,@AddDate,@LastUpdatedDate,@RelationshipProducts,@FileUrl,@TempletId
)
SET @ProductId = @@IDENTITY
INSERT INTO Products_ExProperty([ProductId],[Pattern],[Texture],[Brand],[Style],[Shape],[Inlay])VALUES(@ProductId,@Pattern,@Texture,@Brand,@Style,@Shape,@Inlay)
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @ProductId ---返回ProductId
End
GO
建议使用 if exists(... 检查表是否存在,再执行插入表或回滚操作.
http://topic.csdn.net/u/20090104/18/0174b2c8-3868-4fc7-8a5a-b133e6f6092b.html