Stored proc duplicating data

Giganews Newsgroups
Subject: Stored proc duplicating data
Posted by:  DarkHades (hades…@gmail.com)
Date: 15 Aug 2006

Hi all, I have a stored proc which returns twice the result and I dont
know why. Can someone have a look at the following code?

BTW, I commented the last SELECT/JOIN, cause that one doubled the
result too.

CREATE  procedure ent_tasks_per_user_company (
    @companyName as varchar(50),
    @resourceName as varchar(50)
)
AS

SELECT
tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete

INTO #myTemp

FROM MSP_VIEW_PROJ_TASKS_ENT as tasks

INNER JOIN MSP_OUTLINE_CODES as codes
ON
(
    codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
    AND
    codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
)

INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
ON
(
    taskStd.WPROJ_ID = tasks.WPROJ_ID
    AND
    taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
    --AND
    --taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
)

WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)

/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp

INNER JOIN MSP_OUTLINE_CODES taskCode
ON
(
    taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
)*/

SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
@resourceName + '%'

Thank you!

Chris

Replies