Stored procedure not gettings results expected

-- I need the stored procedure below to return the ObjectEntityID's when there is either a
-- match on the RoleID or the PeopleID.  The problem is I'm only getting a result when there
-- is matches from both tables.



USE [EntityTest]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectEntityCluster]') AND type in (N'U'))
DROP TABLE [dbo].[ObjectEntityCluster]
GO
IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'ObjectEntityCluster')
BEGIN
CREATE TABLE [dbo].[ObjectEntityCluster](
	[ObjectEntityClusterID] [int] IDENTITY(1,1) NOT NULL,
	[GalacticID] [int] NOT NULL,
	[EntityID] [int] NOT NULL,
	[ObjectEntityID] [int] NOT NULL,	
	[Active] [bit] NOT NULL,	
 CONSTRAINT [PK_ObjectEntityCluster] PRIMARY KEY CLUSTERED 
(
	[ObjectEntityClusterID] ASC
))
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectEntityPeopleCluster]') AND type in (N'U'))
DROP TABLE [dbo].[ObjectEntityPeopleCluster]
GO
IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'ObjectEntityPeopleCluster')
BEGIN
CREATE TABLE [dbo].[ObjectEntityPeopleCluster](
	[ObjectEntityPeopleClusterID] [int] IDENTITY(1,1) NOT NULL,
	[ObjectEntityID] [int] NOT NULL,	
	[PeopleID] [int] NOT NULL,
	[Active] [bit] NOT NULL,	
 CONSTRAINT [PK_ObjectEntityPeopleCluster] PRIMARY KEY CLUSTERED 
(
	[ObjectEntityPeopleClusterID] ASC
))
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectEntityRoleCluster]') AND type in (N'U'))
DROP TABLE [dbo].[ObjectEntityRoleCluster]
GO
IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'ObjectEntityRoleCluster')
BEGIN
CREATE TABLE [dbo].[ObjectEntityRoleCluster](
	[ObjectEntityRoleClusterID] [int] IDENTITY(1,1) NOT NULL,
	[ObjectEntityID] [int] NOT NULL,	
	[EntityRoleID] [int] NOT NULL,
	[Active] [bit] NOT NULL,	
 CONSTRAINT [PK_ObjectEntityRoleCluster] PRIMARY KEY CLUSTERED 
(
	[ObjectEntityRoleClusterID] ASC
))
END
GO


-----------------------------------------------------------------------------------------------


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_ObjectEntityClusterTest]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ObjectEntityClusterTest];
GO
CREATE PROCEDURE [dbo].[usp_ObjectEntityClusterTest]
(	
	@EntityRoleID INT,
	@PeopleID   INT
)
AS
BEGIN    
    SELECT c.[ObjectEntityID]
	FROM [dbo].[ObjectEntityCluster] c
	JOIN [dbo].[ObjectEntityRoleCluster] r ON c.[ObjectEntityID] = r.[ObjectEntityID]
	JOIN [dbo].[ObjectEntityPeopleCluster] p ON c.[ObjectEntityID] = p.[ObjectEntityID]	
	WHERE  r.[EntityRoleID] = @EntityRoleID OR p.[PeopleID] = @PeopleID AND c.[Active] = 1
END
GO


-------------------------------------------------------------------------------------------------

DECLARE	@return_value int

EXEC	@return_value = [dbo].[usp_ObjectEntityClusterTest]
		@EntityRoleID = 1,
		@PeopleID = 1

SELECT	'Return Value' = @return_value

GO
------------------------------------------------------------------------------------------------

--Test 1: Run Insert1, do not run Insert2 = no results
--Test 2: Run insert2 = Returns ObjectEntityID

--Insert1
--INSERT INTO [dbo].[ObjectEntityCluster]([GalacticID],[EntityID],[ObjectEntityID],[Active])VALUES(1,1,1,1)
--GO
--INSERT INTO [dbo].[ObjectEntityRoleCluster]([ObjectEntityID],[EntityRoleID],[Active])VALUES(1,1,1)
--GO

--Insert2
--INSERT INTO [dbo].[ObjectEntityPeopleCluster]([ObjectEntityID],[PeopleID],[Active])VALUES(1,1,1)
--GO

Select all Open in new window