STORED PROCEDURE RETURNING TOO MANY ROWS 1

[manuela]

Have written a stored procedure to read table values differently, i.e. the table structure is as follows:

SurveyID QuesttionID Answer
1 3 Yes
1 4 Unsure
2 3 No
2 4 Yes

want to create a temp table to contain the following:

SurveyID Var1 Var2 Var3 Var4 .. varn
1 3 Yes 4 Unsure
2 3 No 4 Yes

there are currently 9 SurveyIDs and the resulting temp table has 81 rows! and I'm not sure why - obviously looping has gone wrong somewhere

any suggestions???
many thanks,

 

[Cagliostro]

Maybe you should return only one row? For example the first one. Sometimes doesn't make sence for return many than one. John Fill

ivfmd@mail.md

 

[manuela]

Thanks John, but the temp table needs to return one row per SurveyID and at present it's returning 9 rows per SurveyID!

 

[Cagliostro]

can you say, where you get the problem?
for example you can't use
select a,b,(select c from t) d from xxx
if d is may rows. If d means only one, is not a problem. You can do
select a,b,(select top 1 c from t) d from xxx or osme other things (max, min, avg ...) to limis the subquery to only one row. Can you say in which place you get the error? John Fill

ivfmd@mail.md

 

[fluteplr]

maybe you could show us the CODE??!!!

 

[manuela]

fluteplr / john fill
many thanks for your responses. here's the code:

CREATE PROCEDURE stp_rptTCOTest

AS

SET NOCOUNT ON

-- Declare variables for values to be picked up from QuestionID 3
DECLARE @SurveyID VarChar(15)
DECLARE @GL1 VarChar(100)
DECLARE @GL2 VarChar(100)
DECLARE @UserName VarChar(30)
DECLARE @Link Int
DECLARE @QuestID3 Int
DECLARE @Answer3 VarChar(255)

-- Declare variables for values to be picked up from QuestionID 4
DECLARE @QuestID4 Int
DECLARE @Answer4 VarChar(255)

-- Declare variables to check @FETCH_STATUS per cursor
DECLARE @Status1 Int -- fetch status for curQuest3
DECLARE @Status2 Int -- fetch status for curQuest4

-- Create temp table to hold results
CREATE TABLE #rpt_TCO (
SurveyID VarChar(15),
GL1 VarChar(100),
GL2 VarChar(100),
UserName VarChar(30),
Link Int,
QuestID3 Int,
Answer3 VarChar(255),
QuestID4 Int,
Answer4 VarChar(255) )

-- Get data for QuestionID 3
DECLARE curQuest3 CURSOR FOR
SELECT datSurvey.SurveyID,
datUser.GroupingLevel1 AS GL1,
datUser.GroupingLevel2 AS GL2,
datUser.UserName,
datCensusAnswer.Link,
datCensusAnswer.QuestionID,
datCensusAnswer.Answer

FROM datSurvey
LEFT OUTER JOIN datCensusAnswer ON datSurvey.Link = datCensusAnswer.Link
LEFT OUTER JOIN datUser ON datSurvey.Link = datUser.Link
INNER JOIN cfgCensusQuestion ON datCensusAnswer.QuestionID = cfgCensusQuestion.QuestionID

WHERE datSurvey.Status <> 'D'
AND cfgCensusQuestion.ShortDesc = 'M1CMoved'

ORDER BY datSurvey.SurveyID

BEGIN

OPEN curQuest3
FETCH curQuest3
INTO @SurveyID, @GL1, @GL2, @UserName, @Link, @QuestID3, @Answer3

SELECT @Status1 = @@FETCH_STATUS

WHILE @Status1 = 0

BEGIN

IF @Status1 = 0

BEGIN
DECLARE curQuest4 CURSOR FOR -- Question 4 values
SELECT datCensusAnswer.QuestionID,
datCensusAnswer.Answer
FROM datSurvey
LEFT OUTER JOIN datCensusAnswer ON datSurvey.Link = datCensusAnswer.Link
INNER JOIN cfgCensusQuestion ON datCensusAnswer.QuestionID = cfgCensusQuestion.QuestionID

WHERE datSurvey.Status <> 'D'
AND cfgCensusQuestion.ShortDesc = 'M1CShared'

ORDER BY datSurvey.SurveyID

OPEN curQuest4
FETCH curQuest4
INTO @QuestID4, @Answer4

SELECT @Status2 = @@FETCH_STATUS


IF @Status2 = 0

WHILE @Status2 = 0

BEGIN
INSERT INTO #rpt_TCO VALUES (@SurveyID, @GL1, @GL2, @UserName,
@Link, @QuestID3, @Answer3, @QuestID4, @Answer4)

FETCH curQuest4
INTO @QuestID4, @Answer4

SELECT @Status2 = @@FETCH_STATUS
END
ELSE

INSERT INTO #rpt_TCO VALUES (@SurveyID, @GL1, @GL2, @UserName, @Link, @QuestID3, @Answer3, NULL, NULL)

CLOSE curQuest4
DEALLOCATE curQuest4

END

FETCH curQuest3
INTO @SurveyID, @GL1, @GL2, @UserName, @Link, @QuestID3, @Answer3

SELECT @Status1 = @@FETCH_STATUS

END


CLOSE curQuest3
DEALLOCATE curQuest3

END

-- return all data in temporary table #rpt_TCO
SELECT * FROM #rpt_TCO


John
will think about your latest response

 

[manuela]

the other thing I notice when I use stored procedure via Crystal Reports is that data returned from cursor curQuest3 is OK but curQuest4 data is not

 

[manuela]

all the procedure seems to be doing is repeating each of the 9 values for curQuest4 per row from curQuest3

 

[Cagliostro]

In my opinion you should insert values not after loop terminating, because each fetch from the cursor always update variables with new values but doesn't store them in variables as arrays. John Fill

ivfmd@mail.md

 

[manuela]

John
many thanks for your replies. I'm only in for the rest of this week for this morning as I have my graduation ceremony today and ball tomorrow so I may still have some queries next week!!!!
once again, many thanks
Manuela