Need Help with SP result

Question

Post reply

Need Help with SP result

rite2shubhra

SSC Journeyman

Points: 94
More actions
April 9, 2009 at 2:48 pm

#130469

I wrote a stored procedure where I am transferring the data in temp table and then removing the Duplicates and then do the select from the temp table. I am not sure why But its giving me 3 result set(though I have only one select). First and third is empty and 2nd is showing the actual data.
First I want to know why I am getting it and second How I can avoid it.
Please help me! If needed I can post the script.
Thanks in advance for your help.
Shubhra

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If it's giving multiple result sets, it's got multiple selects.

Go ahead and post the script. That'll definitely help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Can't help you unless we see the code, so please post it.

rite2shubhra SSC Journeyman Points: 94 More actions · Answer 3

Thanks for the prompt reply.

There are 2 selects but data is going in temp table so I think(I might be wrong) it should not diplay anything.

Here is the script:

ALTER PROCEDURE [dbo].[usp_Billable_summ_PLc_report]

@PD_No int,

@year_no int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--********Get Project Details

SELECT {fn CONCAT({fn CONCAT(T1."PROJ_ID",' ')},T2."PROJ_NAME")} "c1" ,

T3."BILL_LAB_CAT_DESC" "c2" ,

SUM(case when T1."PD_NO" = @PD_No then T1."ACT_HRS" else 0 end ) "c3" ,

SUM(case when T1."PD_NO" = @PD_No then T1."REV_RT_AMT" * T1."ACT_HRS" else 0 end ) "c4" ,

T2."CUST_ID" "c7"

INTO #temp_Report1

FROM deltek.fsacp.deltek."PROJ" T2,

deltek.fsacp.deltek."LAB_HS" T1,

deltek.fsacp.deltek."BILL_LAB_CAT" T3

WHERE T2."PROJ_ID" = T1."PROJ_ID"

AND T1."BILL_LAB_CAT_CD" = T3."BILL_LAB_CAT_CD"

AND T2."BILL_PROJ_FL" = 'Y'

AND T1."FY_CD" = @year_no

AND T1."PROJ_ID" LIKE '%1004.1%'

GROUP BY {fn CONCAT({fn CONCAT(T1."PROJ_ID",' ')},T2."PROJ_NAME")},

T3."BILL_LAB_CAT_DESC",

T2."CUST_ID", T1."PROJ_ID"

ORDER BY 1 asc , 2 asc

--*********Get City,State *****

SELECT DISTINCT

#temp_Report1.c1 AS Project,

T1."org_name" AS "City",

Org_1."org_name" "State",

#temp_Report1.c2 AS PLC,

#temp_Report1.c3 AS CP_Bill_Hours,

#temp_Report1.c4 AS CP_Bill_Dollars,

#temp_Report1.c7 AS Customer

INTO #temp_Report2

FROM #temp_Report1 , deltek.fsacp.deltek.ORG AS T1

,deltek.fsacp.deltek.ORG AS ORG_1

WHERE Substring(#temp_Report1.c1,22,3) = substring(T1.ORG_ID,9,3)

AND Substring(#temp_Report1.c1,19,2) = Substring(ORG_1.ORG_ID,6,2)

AND T1.lvl_no = '4'

AND org_1.lvl_no = '3'

ORDER BY 1 asc , 2 asc

--********** Delete Duplicates

--begin tran

SET ROWCOUNT 1

SELECT @@rowcount

WHILE @@rowcount > 0

DELETE Rec FROM #temp_Report2 as rec

INNER JOIN

( SELECT Project,PLC,CP_Bill_Hours

FROM #temp_Report2

Group by Project,PLC,CP_Bill_Hours

HAVING COUNT(*) > 1)

AS c ON c.Project = rec.Project

AND c.PLC = rec.PLC

AND c.CP_Bill_Hours = rec.CP_Bill_Hours

SET ROWCOUNT 0

--*************Record Selection

SELECT * FROM #temp_Report2

DROP TABLE #temp_Report1

DROP TABLE #temp_Report2

SET NOCOUNT OFF;

End

KewlSiva SSC Enthusiast Points: 198 More actions · Answer 4

Hi,

After Quick review I have found the following issue with the code.

--********** Delete Duplicates

--begin tran

SET ROWCOUNT 1

SELECT @@rowcount

the above statement was returning your first result set just remove/comment the statement to eliminate the unwanted result set

let us know if you still have issue with the code.

rite2shubhra SSC Journeyman Points: 94 More actions · Answer 5

Thanks for your reply, commenting these two rows is not giving me blank data set.

SET ROWCOUNT 1

SELECT @@rowcount

Now I have to figure out some other way to eliminate the duplicates.

KewlSiva SSC Enthusiast Points: 198 More actions · Answer 6

so one of your problem is solved now thats kewl..........

and there is no need to comment the two lines just comment the select @@rowcount statement and you can still use the same delete method to eliminate duplicates, (if the logic is right)..........

SKewl

GSquared SSC Guru Points: 260824 More actions · Answer 7

Try using this to delete dupes. It's MUCH faster.

;with Dupes (Row) as

(select

row_number() over

(partition by Project,PLC,CP_Bill_Hours

order by Project,PLC,CP_Bill_Hours)

from #temp_Report2)

delete from Dupes

where Row > 1;

This assumes you're using SQL 2005 (as per the forum posted to), and not SQL 2000.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

rite2shubhra SSC Journeyman Points: 94 More actions · Answer 8

Thank you so much everyone.

I appreciate everyone's help.

Regards

Shubhra

GSquared SSC Guru Points: 260824 More actions · Answer 9

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon