22,300
社区成员




--申明一个表变量
DECLARE @ItemGUID TABLE(
VItemGUID UNIQUEIDENTIFIER
)
INSERT INTO @ItemGUID
SELECT [ItemGUID]
FROM [Basket_Item]
where UserID = @UserID
INSERT INTO [Receipt_Item]
(RGUID,
Rqty,
Rdate,
UserID
)
SELECT BGUID,
Bqty,
Bdate,
UserID
FROM [Basket_Item] pi inner join @ItemGUID temp
on pi.BGUID = temp.VItemGUID
--调用一个存储过程删除表 [Basket_Item]数据
exec DeleteBasket_Item @UserID
BEGIN TRY
BEGIN TRAN
insert into Receipt (RGUIF,Date,UserID)
select BGUID,date,UserID
from Basket
where UserID = @UserID
--可以插入表Receipt数据
--申明一个表变量
DECLARE @ItemGUID TABLE(
VItemGUID UNIQUEIDENTIFIER
)
INSERT INTO @ItemGUID
SELECT [ItemGUID]
FROM [Basket_Item]
where BGUID in (select BGUID from Basket where UserID = @UserID)
INSERT INTO [Receipt_Item]
(RGUID,
Rqty,
Rdate,
UserID
)
SELECT BGUID,
Bqty,
Bdate,
UserID
FROM [Basket_Item] pi inner join @ItemGUID temp
on pi.BGUID = temp.VItemGUID
--没有插入表Basket_Item数据
--调用一个存储过程删除表 [Basket_Item]数据
exec DeleteBasket_Item @UserID
--删除Basket_Item表数据可以成功
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
END CATCH
养成好习惯.
使用 TRANSACTION
而且你的脚本没有判断,也许
[Basket_Item] pi inner join @ItemGUID temp
on pi.BGUID = temp.VItemGUID
这个是没有结果的。
DECLARE @ItemGUID TABLE(
VItemGUID UNIQUEIDENTIFIER
)
INSERT INTO @ItemGUID
SELECT [ItemGUID] ---先写死
FROM [Basket_Item]
where UserID = @UserID ---先写死id
执行select 看看有没有数据。
create table #ItemGUID
(
VItemGUID UNIQUEIDENTIFIER
)
INSERT INTO #ItemGUID
SELECT [ItemGUID]
FROM [Basket_Item]
where UserID = @UserID
INSERT INTO [Receipt_Item]
(RGUID,
Rqty,
Rdate,
UserID
)
SELECT BGUID,
Bqty,
Bdate,
UserID
FROM [Basket_Item] pi inner join #ItemGUID temp
on pi.BGUID = temp.VItemGUID
--调用一个存储过程删除表 [Basket_Item]数据
exec DeleteBasket_Item @UserID