如何在存储过程中使用游标将值插入表而不获取重复记录? [英] How to insert value to table using cursor in stored procedure without getting duplicate records?

问题描述

我正在尝试使用存储过程中的游标基于起始日期和截止日期参数将记录插入现有但空表中.请让我知道我在下面的 SQL 中做错了什么?

I am trying to insert records into existing but empty table based on from date and to date parameters using cursor in stored procedure. Please let me know what I am doing wrong in the below SQL?

执行此过程时，第一行重复多次.

When executing this procedure I am getting first row duplicated multiple times.

错误:

超过最大存储过程、函数、触发器或视图嵌套级别(limit32)

Maximum stored procedure, function, trigger or view nesting level exceeded (limit32)

代码:

ALTER proc [dbo].[spempmaster] (@date1 datetime,@date2 datetime)
as
Begin

    Set nocount on

    declare @doj datetime
    declare @empname nchar(10)
    declare @managername nchar(10)
    declare @dept varchar(50)  
    declare emp_report15 cursor for

    select convert(varchar(10),convert(smalldatetime,emp.doj,120),103) DOJ, 
    (emp.name + ' ' + emp.lastname)  Name,
    emp1.name Manager_Name, txtdepartment Department
    from empmaster emp
    left outer join tbljobtitles jt 
        on emp.fkjobtitleid = jt.pkjobtitleid,
    tbldepartment td, 
    tblteam t,
    empmaster emp1
    where
        jt.fkteamid = t.pkteamid
        and td.pkdeptid= t.fkdeptid
        and emp.reportingto = emp1.empno
        and emp.doj between @date1 and @date2
    order by doj

    open emp_report15

    fetch emp_report15 into @doj, @empname, @managername, @dept

    while @@fetch_status = 0
    begin
        insert into tblreport (DOJ,emp_name,manager_name,department)
        values(@doj,@empname,@managername,@dept)
    end

    fetch next from emp_report15 into @doj,@empname,@managername,@dept

    close emp_report15

    deallocate emp_report15

end

推荐答案

首先 - 在这种情况下绝对不需要光标.SQL Server 是一个基于集合的系统 - 不要将在过程语言中工作的过程row-by-agonizing-row方法应用于这个基于集合的系统！改用基于集合的方法！

First of all - there's absolutely no need for a cursor in this situation. SQL Server is a set-based system - don't apply the procedural row-by-agonizing-row approach that works in procedural languages to this set-based system! Use a set-based approach instead!

另外:不要将正确的 ANSI join 语法与旧式的、不推荐使用的逗号分隔的表列表 JOIN 方法混合使用.旧样式已被 SQL-92 标准弃用 - 20 多年前！ - 是时候把它扔出窗外并使用正确的 ISO/ANSI 标准了JOIN 语法(INNER JOIN, LEFT OUTER JOIN).

Also: don't mix the proper ANSI join syntax with the old-style, deprecated comma-separated list of tables JOIN approach. That old style has been deprecated with the SQL-92 standard - more than 20 years ago! - about time to toss it out the window and use the proper ISO/ANSI standard JOIN syntax (INNER JOIN, LEFT OUTER JOIN) all the time.

所以基本上，最后 - 你的陈述会是这样的:

So basically, in the end - your statement would be something like:

ALTER PROCEDURE [dbo].[spempmaster] (@date1 DATETIME, @date2 DATETIME)
AS 
  INSERT INTO dbo.tblreport(DOJ, emp_name, manager_name, department)
      SELECT 
          CONVERT(VARCHAR(10), CONVERT(SMALLDATETIME, emp.doj, 120), 103), 
          (emp.name + ' ' + emp.lastname),
          emp1.name Manager_Name, 
          txtDepartment 
      FROM 
          dbo.empmaster emp
      INNER JOIN 
          dbo.empmaster emp1 ON emp.reportingto = emp1.empno
      LEFT OUTER JOIN 
         dbo.tbljobtitles jt ON emp.fkjobtitleid = jt.pkjobtitleid
      LEFT OUTER JOIN
         dbo.tblteam t ON jt.fkteamid = t.pkteamid
      LEFT OUTER JOIN    
         dbo.tbldepartment td ON td.pkdeptid = t.fkdeptid
      WHERE
          emp.doj BETWEEN @date1 AND @date2

至于避免重复:单独运行 SELECT 查询，看看为什么会出现重复.仅从这段代码来看，局外人无法在这里提供有意义的答案 - 这完全取决于您的表中存储了哪些数据.

As for avoiding duplicates: run your SELECT query separately, and see why you're getting duplicates. Just from this code alone, there's no way for outsiders to provide a meaningful answer here - it entirely depends on what data is stored in your tables.

这篇关于如何在存储过程中使用游标将值插入表而不获取重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！