Cursor to store value in temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor to store value in temp table

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-03-20 : 06:06:34

Following sp gives wrong result whats wrong with following cursor?

ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]

@ProjectBenefitID INT

AS

SET NOCOUNT ON

DECLARE @ErrorMsgID INT
DECLARE @ErrorMsg VARCHAR(200)

DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )

DECLARE @AssessBenefitID INT
DECLARE @ActualQuantity INT
DECLARE @ExpectedQuantity INT
DECLARE @ActualQuality VARCHAR(2000)
DECLARE @ExpectedQuality VARCHAR(2000)
DECLARE @AssessFlag CHAR
DECLARE CUR_BENEFIT CURSOR FOR
SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID

OPEN CUR_BENEFIT

FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID

INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)

FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END

CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT

SELECT * FROM @TEMP_BENEFIT

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 06:09:48

What do you mean by wrong result? What is expected output and how current output differs from it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-03-20 : 06:40:14

quote:
Originally posted by harsh_athalye

What do you mean by wrong result? What is expected output and how current output differs from it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

HI.
FOR PROJECTBENEFITID=1
PMPT_AssessBenefit HAS FOUR ROWS
1) ID=6 PB=1 FLAG=E QULITY
ID=7 PB=1 FLAG=A QUANTITY
ID=8 PB=1 FLAG=E QULITY
ID=9 PB=1 FLAG=A QUANTITY
SO OUTPUT SHOULD BE 2 ROWS FOR PB=1 (ROWS I HAVE TO SHOW AS COLUMNS)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 06:49:15

Why this needs cursor and temp table? Can't this be done using pivot query?

Select
AssessBenefitID,
Sum(Case when AssessFlag='A' then Quantity else 0 end) as ActualQuantity,
Sum(Case when AssessFlag='E' then Quantity else 0 end) as ExpectedQuantity,
Sum(Case when AssessFlag='A' then Quality else 0 end) as ActualQuality,
Sum(Case when AssessFlag='E' then Quality else 0 end) as ExpectedQuality
From PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID
Group by AssessBenefitID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources