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

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
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!

Figured it out!!

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

Begin

SET NOCOUNT ON

if ((SELECT     COUNT(*)
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleid
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South')) = 0)

begin

update actor set processed = 0
WHERE actorName in (SELECT     actorname
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South'))

end

SELECT     TOP 1 dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM         dbo.Actor left outer JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South')

update actor set processed = 1
WHERE     actor.actorname = (SELECT     TOP 1 dbo.Actor.ActorName FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South'))

end
GO

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

marty20y ago
Figured it out!!

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

AS

Begin

SET NOCOUNT ON

if ((SELECT     COUNT(*)
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleid
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South')) = 0)

begin

update actor set processed = 0
WHERE actorName in (SELECT     actorname
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South'))

end

SELECT     TOP 1 dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM         dbo.Actor left outer JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South')

update actor set processed = 1
WHERE     actor.actorname = (SELECT     TOP 1 dbo.Actor.ActorName FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division = 'West'
OR dbo.ActorRole.North = '1' AND @division = 'North'
OR dbo.ActorRole.South = '1' AND @division = 'South'))

end
GO