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

Question

你的意思是，这个存储过程：exec  OpreateTB会按照顺序来执行你的表 TB_CMD 中的语句，执行后，更新每个语句在TB_CMD表中的状态，显示是否执行成功，执行时间等信息吗？

Answer 1

嗯，就是这个意思写了一个，但是好像，每个语句执行，就会报错看了一下，因为你的语句sqlevent，都是临时表，用exec执行创建临时表后，后面对临时表进行操作，那么就会报错。因为这些通过exec执行语句，创建的临时表，只是在创建的那个临时表中存在，一旦执行完成后，就自动删除了。

Answer 2

这个存储过程：
create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
asdeclare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @error int
declare @ROWCOUNT intset @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
endgo
--执行
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]

Answer 3

不知道这样行不行，不过看起来不太爽每一次执行的时候都 用try catch处理，手动提交和回滚，在后面调用的写记录的存储过程。

Answer 4

感谢小当家，基本符合了，就不是那么完美。
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]

Answer 5

感谢小当家，基本符合了，就不是那么完美。
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)
asdeclare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @error int
declare @ROWCOUNT intset @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
endgo

Answer 6

再修改一下：
create proc dbo.OpreateTB
--@OpreateUser nvarchar(100),
--@IsRun nvarchar(10)
asdeclare @i int
declare @start datetime
declare @sql nvarchar(max)
declare @OrderNumber int
declare @error int
declare @ROWCOUNT intset @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
endgo