How do I loop through a temp table in a stored procedure with no pk

Question

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:

1. http://ask.sqlservercentral.com/questions/7969/loop-through-records-in-a-temporary-table.html
2. 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

Questions:

1. Is it possible to achieve what I'm attempting without cursors?
2. If it is possible, how would I go about fetching each row with a non unique column?

Answer 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).

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."

Here is how you can do this in 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.

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.

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:

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.

Answer 2

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

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

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

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