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?

I'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, 2006

Cheers Scott, That was very informative. Yeah, unfortunately I'm not a DB developer. Just trying to think this stuff out for myself as we have no DB expertise in work. From the above code I'm trying to sequentially chose the above @actorName so that the workload is spread evenly amongst employees from the different divisions. At the moment it is picking the first one off the database table. Are there any handy stored proc functions that allows a sequential generator of names so that each @actorName in the database will get picked from their appropraite divisons?? Any help would be apprecaited. Kind Regards, BC

...or would this be better?? CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50) AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT dbo.Actor.ActorName, dbo.ActorRole.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 dbo.Actor.ActorName, dbo.ActorRole.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 dbo.Actor.ActorName, dbo.ActorRole.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 RETURN GO

Hi, I'm quite new to stored procedures but have been fine with them up until now(OUTPUT params?? - If that's what I even need!:confused:) when i need to return more that one value to a dataset in my C# code ie. @actorName and @actorLogon. I thought I could do something like this but I get a syntax error on last line... (Msg 102, Level 15, State 1, Procedure sp_GetAssignedDetails, Line 60 Incorrect syntax near ','.) I've tried a few different ways but something tells me this is not the way to go. Here's the code - Any help appreciated. 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 SET @actorName= ( SELECT 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') SET @actorLogon= ( SELECT 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 SET @actorName= ( SELECT 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') SET @actorLogon= ( SELECT 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 SET @actorName= ( SELECT 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') SET @actorLogon= ( SELECT 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 RETURN @actorName, @actorLogon GO ps. What kind of value would this return if I didn't create any Output parameters and just went through the selec

Cheers Guys. Figured it out. Here's a test on a click button for anyone else interested public int GetRandomInt(int[] array, int length) { Random ranNum = new Random(); return array[ranNum.Next(0, length)]; } private void button1_Click(object sender, EventArgs e) { int [] intArray; intArray = new int[3] {123, 321, 666}; int randumNum = GetRandomInt(intArray, 3); randomLabel.Text = (randumNum.ToString()); }

Thanks for your reply. I'm getting the follwoing error.. Error 2 'System.Random.Next()' is a 'method' but is used like a 'type' All I require is just one number returned from the array. Does return array not return a list of arrays or does the Next method refine it to one number? Pardon my ignorance.

Hi, I have an array (which may change in length each time) of numbers (employeeid) and I want to create a random generator function to return one these numbers. The only parameters I will be receiving are the array of numbers and the array length. I'm new to C# so any sample code would be appreciated. Thanks.