我需要对多条记录运行存储过程

发布于 2024-09-03 04:35:52 字数 725 浏览 1 评论 0原文

我需要对一堆记录运行存储过程。我现在的代码迭代存储在临时表中的记录。该存储过程返回一个记录表。

我想知道我能做些什么来避免迭代（如果有的话）。

set @counter = 1     
set @empnum = null     
set @lname = null     
set @fname = null     
-- get all punches for employees     
while exists(select emp_num, lname, fname from #tt_employees where id = @counter)     
begin     
    set @empnum = 0     
    select @empnum = emp_num, @lname = lname , @fname= fname from #tt_employees where id = @counter     

   INSERT @tt_hrs     
   exec PCT_GetEmpTimeSp 
      empnum    
     ,@d_start_dt     
     ,@d_end_dt     
     ,@pMode = 0    
     ,@pLunchMode = 3    
     ,@pShowdetail = 0    
     ,@pGetAll = 1          

  set @counter = @counter + 1     
end

原文

I need to run a stored procedure on a bunch of records. The code I have now iterates through the record stored in a temp table. The stored procedure returns a table of records.

I was wondering what I can do to avoid the iteration if anything.

set @counter = 1     
set @empnum = null     
set @lname = null     
set @fname = null     
-- get all punches for employees     
while exists(select emp_num, lname, fname from #tt_employees where id = @counter)     
begin     
    set @empnum = 0     
    select @empnum = emp_num, @lname = lname , @fname= fname from #tt_employees where id = @counter     

   INSERT @tt_hrs     
   exec PCT_GetEmpTimeSp 
      empnum    
     ,@d_start_dt     
     ,@d_end_dt     
     ,@pMode = 0    
     ,@pLunchMode = 3    
     ,@pShowdetail = 0    
     ,@pGetAll = 1          

  set @counter = @counter + 1     
end

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事灯 2024-09-10 04:35:52

避免这种迭代的一种方法是分析存储过程中的代码并进行修改，以便一次处理所有输入集，而不是一次处理一组输入。通常，这是不可能的，这就是为什么迭代循环并不罕见。

一种可能的替代方法是使用 APPLY 功能（交叉应用、外部应用）。为此，您需要将该过程重写为表类型函数之一，并将该函数应用到查询中，如下所示：（

INSERT @tt_hrs      
 select [columnList]
 from #tt_employees
  cross apply dbo.PCT_GetEmpTimeFunc(emp_num, @d_start_dt, @d_end_dt, 0, 3, 0, 1)

尚不清楚该过程的所有输入来自何处。）

请注意，您仍在迭代对该函数的调用，但现在它被“打包”到一个查询中。

One way to avoid this kind of iteration is to analyze the code within the stored procedure and revised so that, rather than processing for one set of inputs at a time, it processes for all sets of inputs at a time. Often enough, this is not possible, which is why iteration loops are not all that uncommon.

A possible alternative is to use APPLY functionality (cross apply, outer apply). To do this, you'd rewrite the procedure as one of the table-type functions, and work that function into the query something like so:

INSERT @tt_hrs      
 select [columnList]
 from #tt_employees
  cross apply dbo.PCT_GetEmpTimeFunc(emp_num, @d_start_dt, @d_end_dt, 0, 3, 0, 1)

(It was not clear where all your inputs to the procedure were coming from.)

Note that you still are iterating over calls to the function, but now it's "packed" into one query.

回复收藏 0 原文

灯下孤影 2024-09-10 04:35:52

我认为你走在正确的道路上。
您可以有一个带有标识列的临时表，

CREATE TABLE #A (ID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50))

将记录插入到该临时表后，查找表中的记录总数。

DECLARE @TableLength INTEGER
SELECT @TableLength  = MAX(ID) FROM #A

DECLARE @Index INT
SET @Index = 1

WHILE (@Index <=@TableLength)
BEGIN

-- DO your work here 

SET @Index = @Index + 1


END

与您已经提出的类似。
迭代记录的替代方法是使用 CURSOR。应不惜一切代价避免使用游标。

I think you are on the right track.
you can have a temp table with identity column

CREATE TABLE #A (ID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50))

After records are inserted in to this temp table, find the total number of records in the table.

DECLARE @TableLength INTEGER
SELECT @TableLength  = MAX(ID) FROM #A

DECLARE @Index INT
SET @Index = 1

WHILE (@Index <=@TableLength)
BEGIN

-- DO your work here 

SET @Index = @Index + 1


END

Similar to what you have already proposed.
Alternative to iterate over records is to use CURSOR. CURSORS should be avoided at any cost.

回复收藏 0 原文

~没有更多了~