【高分赏格】【难度大】【存储过程】【100分】【顶者都有分】

【高分悬赏】【难度大】【存储过程】【100分】【顶者都有分】

先顶后看
[解决办法]
如果事务回滚，你的记录也会回滚哦
[解决办法]

[解决办法]
帮楼主顶啊
[解决办法]
先顶后看  
[解决办法]
之前公司有个sp，出问题了事务回滚，结果当时记录在日志表中的信息也跟着回滚了
[解决办法]
你的意思是，这个存储过程：exec  OpreateTB

会按照顺序来执行你的表 TB_CMD 中的语句，执行后，更新每个语句在TB_CMD表中的状态，显示是否执行成功，执行时间等信息吗？

[解决办法]


写了一个，但是好像，每个语句执行，就会报错。。。

看了一下，因为你的语句sqlevent，都是临时表，用exec执行创建临时表后，后面对临时表进行操作，那么就会报错。

因为这些通过exec执行语句，创建的临时表，只是在创建的那个临时表中存在，一旦执行完成后，就自动删除了。
[解决办法]
这个存储过程：
create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
as

declare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @error int
declare @ROWCOUNT int 
 

set @i = 1;

while @i <= (select COUNT(*) from [TB_CMD])
begin
   set @sql = (select sqlevent from [TB_CMD] where [OrderNumber] = @i)
   
   set @start = GETDATE()
   
   exec(@sql);
   
   select @error = @@ERROR,
          @ROWCOUNT = @@ROWCOUNT         
          
   update [TB_CMD]
   set BetweenTime = datediff(ms,@start,GETDATE()),
       RunLog = case when @error = 0 then '（'+cast(@ROWCOUNT as varchar)+'行影响）'
                     else '回滚'
                end,
       RunStatus = case when @error = 0 then 1
                        when @error <> 1  then 0
                   end,
       IsRun = 1
   where [OrderNumber] = @i
   
   set @i = @i + 1
end

go


--执行
exec  OpreateTB 
/*

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 105，级别 15，状态 1，第 1 行
字符串 '' 后的引号不完整。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 3701，级别 11，状态 5，第 1 行
无法对 表 '#tb' 执行 删除，因为它不存在，或者您没有所需的权限。

(1 行受影响)

*/

--查询
select *
from [TB_CMD]

[解决办法]
不頂都不行了.
[解决办法]
不知道这样行不行，不过看起来不太爽

每一次执行的时候都 用try catch处理，手动提交和回滚，在后面调用的写记录的存储过程。

[解决办法]
Quote: 引用:

这个存储过程：
create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
as

declare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @error int
declare @ROWCOUNT int

set @i = 1;

while @i <= (select COUNT(*) from [TB_CMD])
begin
   set @sql = (select sqlevent from [TB_CMD] where [OrderNumber] = @i)
   
   set @start = GETDATE()
   
   exec(@sql);
   
   select @error = @@ERROR,
          @ROWCOUNT = @@ROWCOUNT         
          
   update [TB_CMD]
   set BetweenTime = datediff(ms,@start,GETDATE()),
       RunLog = case when @error = 0 then '（'+cast(@ROWCOUNT as varchar)+'行影响）'
                     else '回滚'
                end,
       RunStatus = case when @error = 0 then 1
                        when @error <> 1  then 0
                   end,
       IsRun = 1
   where [OrderNumber] = @i
   
   set @i = @i + 1
end

go


--执行
exec  OpreateTB 
/*

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。 
 

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 105，级别 15，状态 1，第 1 行
字符串 '' 后的引号不完整。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 208，级别 16，状态 0，第 1 行
对象名 '#tb' 无效。

(1 行受影响)
消息 3701，级别 11，状态 5，第 1 行
无法对 表 '#tb' 执行 删除，因为它不存在，或者您没有所需的权限。

(1 行受影响)

*/

--查询
select *
from [TB_CMD]


感谢小当家，基本符合了，就不是那么完美。
1.OrderNumber 里面排序不一定一直递增1的，如果被删除某条记录。中间就会空缺那个数值
2.我做了一个简单的测试
delete from TB_CMD
if object_id('tb') is not null drop table tb
insert into   tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('create table tb (id int ,name varchar(10))',1,0,'Tom')
insert into   tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser  )values('insert into tb select 1,''test1''',2,0,'Tom')
insert into   tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser  )values('insert into tb select.  2,''test2''',3,0,'Tom')
exec  OpreateTB 
select *
from [TB_CMD]




呵呵，改了一下，按照orderNumber来排序：
create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
as

declare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @error int
declare @ROWCOUNT int

set @i = 1;

while @i <= (select COUNT(*) from [TB_CMD])
begin

   --按照[OrderNumber]进行了排序，每次取出1条
   ;with t
   as
   (
     select *,
            ROW_NUMBER() over(order by [OrderNumber]) rownum  
     from [TB_CMD]
   )
   
   select  @sql = [SqlEvent]
   from t
   where rownum = @i
   
   set @start = GETDATE()
   
   exec(@sql);
   
   select @error = @@ERROR,
          @ROWCOUNT = @@ROWCOUNT         
          
   update [TB_CMD]
   set BetweenTime = datediff(ms,@start,GETDATE()),
       RunLog = case when @error = 0 then '（'+cast(@ROWCOUNT as varchar)+'行影响）'
                     else '回滚'
                end,
       RunStatus = case when @error = 0 then 1
                        when @error <> 1  then 0
                   end,
       IsRun = 1
   where [OrderNumber] = @i
   
   set @i = @i + 1
end

go

[解决办法]
再修改一下：


create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
as

declare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @OrderNumber int
declare @error int
declare @ROWCOUNT int

set @i = 1;

while @i <= (select COUNT(*) from [TB_CMD])
begin

   --按照[OrderNumber]进行了排序，每次取出1条
   ;with t
   as
   (
     select *,
            ROW_NUMBER() over(order by [OrderNumber]) rownum  
     from [TB_CMD]
   )
   
   select  @sql = [SqlEvent],
           @OrderNumber = [OrderNumber]
   from t
   where rownum = @i
   
   set @start = GETDATE()
   
   exec(@sql);
   
   select @error = @@ERROR, 
 
          @ROWCOUNT = @@ROWCOUNT         
          
   update [TB_CMD]
   set BetweenTime = datediff(ms,@start,GETDATE()),
       RunLog = case when @error = 0 then '（'+cast(@ROWCOUNT as varchar)+'行影响）'
                     else '回滚'
                end,
       RunStatus = case when @error = 0 then 1
                        when @error <> 1  then 0
                   end,
       IsRun = 1
   where [OrderNumber] = @OrderNumber  --这里也修改了
   
   set @i = @i + 1
end

go