CAMPzxzxDeathzxzx
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just for kicks and giggles define for us how you believe we would know what 'results as expected' means without actually defining it: Vulcan Mind Meld, 'The Force', or interpretive dance.
Jim, look at the first 3 lines of the code
Ahh. You are correct Sir. In that case changing the JOINs to LEFT JOINs with the WHERE .. OR should do it, assuming the columns in the WHERE = @parameters meet the definition of 'matching'.