Posted by DarkHades on 08/15/06 13:28

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

• Next in forum: Re: clustered vs. non clustered
• Prev in forum: Re: Almost there with bulk insert
• Thread view: Stored proc duplicating data

[Reply to this message]