asked on
-- 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