再接再厉。
前面我已经使用了临时表添加了 ##tmpCashPurchaseDetail (这个是我导入excel的资料）
- SQL code
create proc sp_tmpCashPurchaseDetailUpdateasUPDATE CPDSET CPD.ItemID = TMPCPD.ItemIDFROM CashPurchaseDetail AS CPD INNER JOIN ##tmpCashPurchaseDetail AS TMPCPD ON CPD.CashPurchaseNo = TMPCPD.CashPurchaseNo AND CPD.LineID = TMPCPD.LineID AND TMPCPD.VerifyIUD = 0go
请问我的语句有错吗？ Inner join 是对吗？ 还是需要变成这样
- SQL code
create proc sp_tmpCashPurchaseDetailUpdateasUPDATE CPDSET CPD.ItemID = TMPCPD.ItemIDFROM CashPurchaseDetail AS CPD INNER JOIN ##tmpCashPurchaseDetail AS TMPCPD ON CPD.CashPurchaseNo = TMPCPD.CashPurchaseNo AND CPD.LineID = TMPCPD.LineID AND TMPCPD.VerifyIUD = 0 Cross Join CPDgo
我的程式运行的时候会导入到##tmpCashPurchaseDetail （因为conn会断掉，关闭后会清空，所以使用##）
之后再运行存储sp_tmpCashPurchaseDetailUpdate，如果发现条件成立，那么就会update
但是我担心没有运行到全部Row，所以希望大家帮我看一下，谢谢
存储完毕之后，就会把conn断掉，那么就会自动清空了。
如果这个算批量导入，那么下面这个是不是更好（参考）这个还有分析。
- SQL code
ALTER PROCEDURE [dbo].[SP_User_Hierarchy_Expend2_Dr2] (@current nvarchar(50)) AS BEGIN SET NOCOUNT ON DECLARE @lvl int, @line nvarchar(50), @parent nvarchar(50) CREATE TABLE #stack (item nvarchar(50), lvl int) CREATE TABLE #stack2 (item nvarchar(50), parent nvarchar(50), lvl int) INSERT INTO #stack VALUES (@current, 1) SELECT @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl) BEGIN SELECT @current = item FROM #stack WHERE lvl = @lvl SELECT @line = @current SELECT @parent = Parent FROM SP_User_Hierarchy WHERE Child=@current insert #stack2 values(@current, @parent, @lvl) DELETE FROM #stack WHERE lvl = @lvl AND item = @current INSERT #stack SELECT Child, @lvl + 1 FROM SP_User_Hierarchy WHERE Parent = @current IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END -- WHILE Begin SELECT lvl, aspnet_Users.UserName as UserName, aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, SP_User_Manager.ManagerType, SP_User_Manager.County, SP_User_Manager.Zip, SP_User_Manager.State, SP_User_Manager.LastName + ', ' + SP_User_Manager.FirstName AS FullName FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN SP_User_Manager ON aspnet_Users.UserName = SP_User_Manager.UserName INNER JOIN #stack2 on item = SP_User_Manager.UserName END END
------解决方案--------------------
#1.如果此存储过程是放在一个事务中执行,#4出错时会回滚.
#2.如果存储过程中的#3,#4步放在一个事务中,#4出错时会回滚.
begin tran
--exec 你有存储过程
end tran