How to do a sequential select in a stored procedure? [modified]
-
Hi, I have a stored proc(below) which assigns tasks to an employee depending on which division they are in. This is working fine, but is only selecting the first name in the database it comes to. In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee?? ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process. I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others. Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50), @actorName nVarChar(50) OUTPUT, @actorLogon nVarChar(5) OUTPUT AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT @ActorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' END-- modified at 16:05 Tuesday 31st October, 2006 -
Hi, I have a stored proc(below) which assigns tasks to an employee depending on which division they are in. This is working fine, but is only selecting the first name in the database it comes to. In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee?? ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process. I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others. Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50), @actorName nVarChar(50) OUTPUT, @actorLogon nVarChar(5) OUTPUT AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT @ActorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' END-- modified at 16:05 Tuesday 31st October, 2006I've had an idea (off the top of my head so bear with me :doh:). What if I create a temp table. Then everytime I do a select on the main table I insert the actor to the temp table then everytime I go to assign details I do a check if actor exists in temp table get next person from main table repeat this until we get to the end of the table and do somekind of compare count on both tables to establish we have gone through everyone. Then drop the temp table, re-create it and start it all again?? Clear as mud!! Anyone know best way to compare the count on the two tables so I can then clear the temp table and start over again. Or if anyone has a better idea please let me know
-
Hi, I have a stored proc(below) which assigns tasks to an employee depending on which division they are in. This is working fine, but is only selecting the first name in the database it comes to. In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee?? ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process. I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others. Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50), @actorName nVarChar(50) OUTPUT, @actorLogon nVarChar(5) OUTPUT AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT @ActorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' END-- modified at 16:05 Tuesday 31st October, 2006I'm not sure I understand what you are trying to do regarding 'assigning tasks to an employee depending on which division they are in', but this code does the same thing as that which you provided...only it is a bit shorter.
CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
ASSELECT @ActorName = dbo.Actor.ActorName,
@actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and '1' = case when @division = 'North' then dbo.ActorRole.North
when @division = 'South' then dbo.ActorRole.South
when @division = 'West' then dbo.ActorRole.West end--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
I'm not sure I understand what you are trying to do regarding 'assigning tasks to an employee depending on which division they are in', but this code does the same thing as that which you provided...only it is a bit shorter.
CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
ASSELECT @ActorName = dbo.Actor.ActorName,
@actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and '1' = case when @division = 'North' then dbo.ActorRole.North
when @division = 'South' then dbo.ActorRole.South
when @division = 'West' then dbo.ActorRole.West end--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Thanks for that EricDV. Basically what I'm trying to do is...... If I have 20 employees in my Actor Table I need to select one that hasn't been selected before each time I go to the Actor Table. This is required so that a different employee is assigned a task every time I call the sp_GetAssignedDetails from my application. Once I get to the last employee and assign him/her a task, all employees have now had a task assigned so I then want to go back to employee No.1 and start all over again. Any ideas?
-
Thanks for that EricDV. Basically what I'm trying to do is...... If I have 20 employees in my Actor Table I need to select one that hasn't been selected before each time I go to the Actor Table. This is required so that a different employee is assigned a task every time I call the sp_GetAssignedDetails from my application. Once I get to the last employee and assign him/her a task, all employees have now had a task assigned so I then want to go back to employee No.1 and start all over again. Any ideas?
You could have a datetime field (LastAssignment) in the Actor table. Then, assign the next task to the oldest LastAssignment Actor, and update their LastAssignment.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Hi, I have a stored proc(below) which assigns tasks to an employee depending on which division they are in. This is working fine, but is only selecting the first name in the database it comes to. In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee?? ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process. I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others. Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50), @actorName nVarChar(50) OUTPUT, @actorLogon nVarChar(5) OUTPUT AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT @ActorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SELECT @actorName = dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' SELECT @actorLogon = dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' END-- modified at 16:05 Tuesday 31st October, 2006Here is an idea: - Create a new table PickOrder with 2 columns - division and chosenName. - Iniitialize PickOrder with 4 records - ('North', 'a1') ('South', 'a1') ('East', 'a1') ('West, 'a1') Now, in sp_GetAssignedDetails, 1- Get the chosenName value (cn) from the PickOrder table for the requested division. 2- Using your query, select the top 1 record WHERE actorName > cn ... ORDER BY actorName 3- if no record is found in step 2 and there are existing records for that division (i.e., we have exhuasted the list), set the chosenName to 'a1' and repeat step 2 to start with the first name. 4- Update the chosenName column value in PickOrder table with the actorName found in step 2, so that next time we can pick the next name. -- modified (added ORDER BY clause) at 22:08 Thursday 2nd November, 2006
-- Damodar Periwal Software Tree, Inc. Simplify Data Integration