marty

marty

  • NA
  • 3
  • 0

Create/Insert/Compare temp table with main table in stored procedure?

Nov 1 2006 2:40 PM
Hi,
I have the following stored procedure.....

CREATE PROCEDURE p_GetAssignedDetails
(
@roleName nVarChar(50),
@division nVarChar(50)
)
AS

SET NOCOUNT ON

SELECT TOP 1 dbo.Actor.ActorName,
dbo.ActorRole.ActorLogon
FROM dbo.Actor
INNER JOIN dbo.ActorRole ON dbo.ActorRole.ID = dbo.Actor.ID AND dbo.ActorRole.RoleName = @roleName
WHERE <some criteria for excluding already assigned actors>
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South'
OR dbo.ActorRole.East = '1' AND @division = 'East')
ORDER BY NEWID()


In WHERE statement I need someway in order to get only actors that have not already gone through the select, something like (IF EXISTS (SELECT , etc...).  This is so workload is spread evenly to employees.

I was thinking of when a select is done I would insert actorname and actorlogon details into
a tempActor table, so whenever the next select occurs it checks the temp table against the main table so as not to pick the same person again.  When I finally get to the end of my actors table I need something like when tempActor count = main Actor table count clear the tempTable and start again.

All sounds good but anyone any idea how best to do it??

Thanks in Advance!

Answers (1)