Iterate Through Each Record

I have an ASP.NET page which repeatedly calls a SQL stored procedure that pulls a random userID from the table Hearings_Users. What I need now is to pull each UserID in successive order. I would go down the list of UserIDs in the table until the VB function stopped calling it. I’m not sure how to do this in SQL. Thanks

Private Function GetRandomUser() As String
    Dim UserID As String = ""
    cmd = New SqlCommand("p_Hearings_ActiveUsers", con)
    cmd.CommandType = CommandType.StoredProcedure
    ....
        While dr.Read()
                UserID = dr("UserID")
            End While
        End Using   
    ....     
    Return UserID
End Function

--p_Hearings_ActiveUsers
SELECT TOP 1 UserID FROM Hearings_Users
WHERE Active = 1
ORDER BY NEWID()

Answer

I’d create a new stored procedure that takes a parameter of the last userID, and returns the next in sequence

CREATE PROCEDURE spNextUser(@LastUserID INT) 

The body would be like p_Hearings_ActiveUsers except it has a condition that the ID is greater than the last used

SELECT TOP 1 UserID 
FROM Hearings_Users
WHERE Active = 1 
    AND UserID > @LastUserID
ORDER BY UserID 

When you call this from code, you’ll need to add a parameter to the command

Dim paramUserID As SqlParameter = New SqlParameter("LastUserID", SqlDbType.Integer)
paramUserID.Value = dr(UserID)
cmd.Parameters.Add(paramUserID)

but it’s otherwise just like the call you have already

This approach insures the state is stored in your application, not server side, so if 2 instances are iterating through they don’t cross each other up

Efficiency tip: You don’t need to redefine the parameter each loop iteration, you can define it above the loop and inside the loop just update it’s value.