如何在没有pk的存储过程中循环访问临时表

Problem:

问题：

I need to loop through the records in one table, pulling the employee number and comparing this employee number against another table to see if they're still active employees. If they are no longer active employees I need to pass the data from this row into another stored proc.

我需要在一个表中循环记录，提取员工编号并将该员工编号与另一个表进行比较，看看他们是否仍然是活跃的员工。如果他们不再是活跃的员工，我需要将此行中的数据传递到另一个存储过程。

Research:

研究：

I've googled around quite a bit and realize that I shouldn't use cursors for this. I did however, find the following examples:

我已经搜索了很多，并意识到我不应该使用游标。但是，我找到了以下示例：

http://ask.sqlservercentral.com/questions/7969/loop-through-records-in-a-temporary-table.html
http://ask.sqlservercentral.com/questions/7969/loop-through-records-in-a-temporary-table.html
http://eedle.com/2010/11/11/looping-through-records-in-sql-server-stored-procedure/
http://eedle.com/2010/11/11/looping-through-records-in-sql-server-stored-procedure/

However, it seems like they use a pk to loop through the records. Employee numbers can be the same for multiple recods in my scenario

但是，似乎他们使用pk循环记录。在我的方案中，多个recods的员工编号可以相同

Questions:

问题：

Is it possible to achieve what I'm attempting without cursors?
是否有可能实现我没有游标的尝试？
If it is possible, how would I go about fetching each row with a non unique column?
如果可能，我将如何使用非唯一列获取每一行？

5 个解决方案

#1

Since you haven't given us a full description of your situation, we cannot give a complete answer, however, in general, it's Loops that you want to avoid in a set-based language like SQL and not Cursors per se (the problem with Cursosr is that they require the Loops).

既然你还没有完整地描述你的情况，我们就无法给出一个完整的答案，但是，一般来说，它是你想要在基于集合的语言中避免的循环，比如SQL而不是Cursors本身（问题是Cursosr是他们需要循环）。

In your comments you provide a little bit more information in that you want to "loop through first table, compare to second table, and when the compare fails i delete records from first table. In essense I'm deleting recods from the first table of employees who are no longer with the company."

在你的评论中，你提供了一些信息，你想要“循环第一个表，与第二个表比较，当比较失败时，我从第一个表中删除记录。在essense我正在从第一个表中删除recods不再与公司合作的员工。“

Here is how you can do this in SQL:

以下是如何在SQL中执行此操作：

DELETE  From FirstTable
WHERE   FirstTable.EmployeeID NOT IN
    (
        SELECT  SecondTable.EmployeeID 
        FROM    SecondTable
        WHERE   SecondTable.Flag = 'Y'
    )

No Loops are needed ...

不需要循环......

If the issue then is that you want to use a pre-existing Stored Procedure to do the deletion, then there are a several possibilities:

如果问题是您想要使用预先存在的存储过程进行删除，那么有几种可能性：

First, you can extract the contents of the Stored Procedure and re-write them for these preceding WHERE conditions. I understand that this is code duplication and that it violates some people's DRY instincts, however, understand that SQL is NOT an Object-Oriented development environment and that sometimes code duplication has to happen.

首先，您可以提取存储过程的内容，并为这些先前的WHERE条件重新编写它们。我知道这是代码重复，它违反了某些人的DRY本能，但是，要了解SQL不是面向对象的开发环境，有时代码重复必须发生。

The second option would be to refactor the stored procedure so that it could accept a TableParameter for its EmployeeId's to Delete. This is complicated though, and we would need to see that stored procedure to advise you on it.

第二种选择是重构存储过程，以便它可以接受TableParameter以使其EmployeeId删除。虽然这很复杂，但我们需要查看存储过程以便为您提供建议。

The third option would be to use string aggregation to build dynamic SQL to call the stored procedure for each EmployeeID to be deleted like so:

第三种选择是使用字符串聚合来构建动态SQL，以便为每个EmployeeID调用存储过程，如下所示：

DECLARE @sql As NVarchar(MAX);  
SET     @sql = N'';

SELECT  @sql = @sql + ' 
    EXEC YourProc ''' + CAST(EmployeeID As NVARCHAR(MAX)) + '''; '
FROM    FirstTable
WHERE   FirstTable.EmployeeID IN
    (
        SELECT  SecondTable.EmployeeID 
        FROM    SecondTable
        WHERE   SecondTable.Flag = 'Y'
    )

EXEC(@sql);

This avoids both the Looping and the Cusror problems, though many dislike it also. I prefer this solution myself, largely because of its generality.

这避免了循环和Cusror问题，尽管许多人也不喜欢它。我自己更喜欢这个解决方案，主要是因为它的普遍性。

#2

This will delete all records from your employee data table if there are no matching rows in your current employees table.

如果当前employees表中没有匹配的行，这将删除员工数据表中的所有记录。

I'd sugest you replace the DELETE FROM with SELECT * FROMand then when you're happy to delete the results change it back to DELETE

我想你用SELECT * FROM替换DELETE FROM然后当你乐意删除结果时将其改回DELETE

DELETE FROM
    EmployeeDataTable
WHERE
    NOT EXISTS
    (SELECT 
        NULL
    FROM
        CurrentEmployees
    WHERE
        EmployeeDataTable.EmployeeID = CurrentEmployees.EmployeeID
    )

EDIT: Just saw your comment about the Active flag, this means the query can be changed to

编辑：刚刚看到你对Active标志的评论，这意味着可以将查询更改为

DELETE FROM
    EmployeeDataTable
WHERE
    EXISTS
    (SELECT 
        NULL
    FROM
        CurrentEmployees
    WHERE
        EmployeeDataTable.EmployeeID = CurrentEmployees.EmployeeID
        CurrentEmployees.IsActive <> 'Y'
    )

#3

I would do this by looping through a cursor. You can also add a unique ID to the cursor so you know which row you are currently on.

我会通过循环游标来做到这一点。您还可以向光标添加唯一ID，以便知道当前所在的行。

DECLARE @id uniqueidentifier 
DECLARE @empName   VARCHAR(50)

SELECT newId() AS Id, *
    INTO #mytemp
    FROM MyEmployees
    ORDER BY EmpName

while EXISTS(SELECT TOP 1 1 FROM #mytemp)
BEGIN
    --Get row from cursor to process
    SELECT TOP 1 @id = Id, @empName = EmpName FROM #mytemp  

    --Do you processing here. Call other stored proc.

    --Remove processed row from cursor.
    DELETE FROM #mytemp WHERE Id = @id  
END
DROP TABLE #mytemp

#4

Have you considered using the MERGE statement : http://technet.microsoft.com/en-us/library/bb510625.aspx

您是否考虑过使用MERGE声明：http：//technet.microsoft.com/en-us/library/bb510625.aspx

You have clauses for :

你有以下条款：

When the data does match between both table
当两个表之间的数据匹配时
When the data exists in source, but not in target
当数据存在于源中时，但不在目标中
When the data exists in target, but not in source
当数据存在于目标中时，但不存在于源中

You can then insert, update or delete records depending on the match type. You can also output the action from match to a temporary table for additional custom operations.

然后，您可以根据匹配类型插入，更新或删除记录。您还可以将匹配操作输出到临时表以进行其他自定义操作。

For instance, you can do :

例如，你可以这样做：

MERGE INTO Table1
USING Table2 ON Table1.EmployeeID = Table2.EmployeeID
WHEN MATCHED 
    THEN UPDATE SET Table1.SomeField = Table2.SomeOtherField
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
WHEN NOT MATCHED BY TARGET
    THEN Insert (Name, Status) VALUES (EmployeeName, 'Active')

#5

It would be possible if you took the content from the sproc you are wanting to call inside your cursor and implemented that logic within your current sproc. At this point, you should be able to use set-based logic. Is the sproc you are calling very complex?
如果您从您想要在游标中调用的sproc中获取内容并在当前sproc中实现该逻辑，则是可能的。此时，您应该能够使用基于集合的逻辑。您称之为的sproc非常复杂吗？
What about adding an identity column to the temp table?
如何将标识列添加到临时表？

Sometimes, cursors are the right solution for a variety of reasons.

有时，由于各种原因，游标是正确的解决方案。

#1