使用循环在SQL Server 2008中插入多行？

debugcn 发表于 Dev

猫通维

我有一个临时表来存储一些数据以计算工资。数据与许多表相关，并且是很长的查询逻辑。

此存储过程是从其他表中获取一些数据，然后插入到PayrollTemp表中以备后用。它需要3个参数：开始和结束日期，以及EmployeeID主查询和嵌套查询的（EmpID）。

因此，每当我们运行此存储过程时，我们只会得到

（影响1行）

我的问题是如何在SQL Server中使用循环EmployeeID顺序传递每个（EmpID）存储过程，以便将每个记录插入PayrollTemp表中。

Create PROCEDURE [dbo].[sp_PayrollTemp_Init]
@startDate date, @endDate date, @EmpID int

AS

INSERT INTO dbo.PayrollTemp([EmpID],[BasicSalary],[Allowance],[W_Hrs],[W_days],[OT150Hrs],[OT200Hrs],[OT300Hrs],[Night130Hrs],
[NightOT150Hrs],[NightOT200Hrs],[NightOT300Hrs],[WeekdayDutyDays],[WeekendDutyDays],[OtherEarning],[SI],[HI],[UI],[UnionFee],[DependentDeduction],[OtherDeduction])
        select e.Id,e.BasicSalary,e.Allowance,
        (select SUM(ts.NormalHours) from TimeSheetA ts where EmpID=@EmpID)W_Hrs,
        (SELECT
           (DATEDIFF(dd, @startDate, @endDate) + 1)
          -(DATEDIFF(wk, @startDate, @endDate) * 2)
          -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday' THEN 1 ELSE 0 END)
          -(CASE WHEN DATENAME(dw, @endDate) = 'Saturday' THEN 1 ELSE 0 END))W_days,
        (select SUM(ts.OT150Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT150Hrs,
        (select SUM(ts.OT200Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT200Hrs,
        (select SUM(ts.OT300Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT300Hrs,
        (select SUM(ts.Night130Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)Night130Hrs,
        (select SUM(ts.NightOT150Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT150Hrs,
        (select SUM(ts.NightOT200Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT200Hrs,
        (select SUM(ts.NightOT300Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT300Hrs,

        (select count(EmpID)WeekdayDuty from NightDutySchedule where DATENAME(DW,DutyDate) not in('Saturday','Sunday') and DutyDate between @startDate and @endDate and EmpID=@EmpID)WeekdayDutyDays,
        (select COUNT(EmpID) from NightDutySchedule where DATENAME(DW,DutyDate)='Saturday' or DATENAME(DW,DutyDate)='Sunday' and DutyDate between @startDate and @endDate and EmpID=@EmpID)WeekendDutyDays,
        (select SUM(EarningAmount) from EarningDetails where EarningDate between @startDate and @endDate and EmpID=@EmpID)OtherEarning,
        (SELECT case e.Is_Probation
            WHEN  'False' THEN
            e.BasicSalary*0.08
            ELSE 0 end 
          )SI,
          (SELECT case e.Is_Probation
            WHEN  'False' THEN
            e.BasicSalary*0.015
            ELSE 0 end 
          )HI,
          (SELECT case e.Is_Probation
            WHEN  'False' THEN
            e.BasicSalary*0.01
            ELSE 0 end 
          )UI,
          (SELECT case e.Is_Probation
            WHEN  'False' THEN
            case  when e.BasicSalary > 4000000 or e.BasicSalary = 4000000 then  20000   else 15000 end
            ELSE 0 end 
          )UnionFee,
          (SELECT case e.Is_Probation
            WHEN  'False' THEN
                e.Dependants * 3600000
            ELSE 0 end 
          )DependentDeduction,
         (select SUM(DeductionAmount) from DeductionDetails where DeductionDate between @startDate and @endDate and EmpID=@EmpID)OtherDeduction
          from Employee e where e.Is_Active='True' and e.Foreigner='False' and e.Id=@EmpID

   declare @startDate date, @endDate date, @EmpID int
    set @startDate='2015-09-01'
    set @endDate='2015-09-30'
    set  @EmpID='3'
    select e.Id,e.BasicSalary,e.Allowance,SUM(ts.NormalHours)W_Hrs,
    (SELECT
               (DATEDIFF(dd, @startDate, @endDate) + 1)
              -(DATEDIFF(wk, @startDate, @endDate) * 2)
              -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday' THEN 1 ELSE 0 END)
              -(CASE WHEN DATENAME(dw, @endDate) = 'Saturday' THEN 1 ELSE 0 END))W_days,
        SUM(ts.OT150Hrs)OT150Hrs,SUM(ts.OT200Hrs)OT200Hrs, SUM(ts.OT300Hrs)OT300Hrs,SUM(ts.Night130Hrs) Night130Hrs,
        SUM(ts.NightOT150Hrs)NightOT150Hrs,SUM(ts.NightOT200Hrs)NightOT200Hrs,SUM(ts.NightOT300Hrs)NightOT300Hrs,
        (SELECT COUNT(case 
                WHEN DATENAME(DW,sch.DutyDate) not in('Saturday','Sunday') THEN
                    1
                ELSE 0 end 
              )WeekdayDutyDays,

        (SELECT COUNT(case 
                WHEN DATENAME(DW,sch.DutyDate)  in('Saturday','Sunday') THEN
                    1
                ELSE 0 end 
              )WeekendDutyDays,
        SUM(ed.EarningAmount)OtherEarning,
        (SELECT case e.Is_Probation
                WHEN  'False' THEN
                e.BasicSalary*0.08
                ELSE 0 end 
              )SI,
              (SELECT case e.Is_Probation
                WHEN  'False' THEN
                e.BasicSalary*0.015
                ELSE 0 end 
              )HI,
              (SELECT case e.Is_Probation
                WHEN  'False' THEN
                e.BasicSalary*0.01
                ELSE 0 end 
              )UI,
              (SELECT case e.Is_Probation
                WHEN  'False' THEN
                case  when e.BasicSalary > 4000000 or e.BasicSalary = 4000000 then  20000   else 15000 end
                ELSE 0 end 
              )UnionFee,
              (SELECT case e.Is_Probation
                WHEN  'False' THEN
                    e.Dependants * 3600000
                ELSE 0 end 
              )DependentDeduction,
             SUM(ded.DeductionAmount) OtherDeduction

    from Employee e 
    inner join TimeSheetA ts
    on e.Id=ts.EmpID and e.Is_Active='True' and e.Foreigner='False' 
    inner join NightDutySchedule sch
    on e.Id=sch.EmpID and sch.DutyDate >= @startDate and sch.DutyDate <= @endDate
    inner join  EarningDetails ed
    on e.Id=ed.EmpID and ed.EarningDate >= @startDate and ed.EarningDate <= @endDate
    inner join DeductionDetails ded
    on e.Id= ded.EmpID and ded.DeductionDate >= @startDate and ded.DeductionDate <= @endDate

我修改为使用内部连接语句，但是当我执行命令时，它显示如下错误：

    Msg 102, Level 15, State 1, Line 62
Incorrect syntax near '@endDate'.

第62行是：在e.Id = ded.EmpID和ded.DeductionDate> = @startDate和ded.DeductionDate <= @endDate上（这是最后一个，为什么？）

伊斯梅尔·富恩特斯（Ismael Fuentes）

您必须使用游标：MSDN-游标

一些示例代码：

DECLARE cursorName
CURSOR FAST_FORWARD FOR SELECT a,b FROM table WHERE a > 1
DECLARE @a int
DECLARE @b varchar(10)
OPEN cursorName
FETCH NEXT FROM cursorName
INTO @a,
    @b

WHILE ( @@FETCH_STATUS = 0 )  BEGIN
--Code and procedure you want to call with @a and @b for each row
FETCH NEXT FROM cursorName
INTO @a, @b
END
CLOSE cursorName
DEALLOCATE cursorName

希望能帮助到你：）

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。