Nested Cursors - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Nested Cursors

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-17 : 15:30:53

Hello -
Below is a set of nested cursors i'm having problems with. Basically, I can get results from the initial cursor named: SURVEYS. But, when I drop down into the 2nd and 3rd cursor none of my variables are getting populated. Any ideas?

thanks
dan

CREATE PROCEDURE [dbo].[sp_CalculateResults]

AS
Declare @studentResult decimal
Declare @staffResult decimal
Declare @parentResult decimal
Declare @SurveyID int
Declare @SchoolID int
Declare @QuestionID int
Declare @QuestionNo int
Declare @Respondent char(1)
Declare @counter smallint

DECLARE SURVEYS CURSOR FOR
-- Loop through surveys
select SurveyID, SchoolID = SchoolID from Survey where status = 'C'
OPEN SURVEYS
FETCH NEXT FROM SURVEYS INTO @SurveyID, @SchoolID
WHILE @@FETCH_STATUS <> 0
Begin
--Loop through questionNos
Declare QuestionNo Cursor For
select DISTINCT(QuestionNO) from Question where CustomSchoolID IN (101, @SchoolID) order by QuestionNO asc
Open QuestionNo
FETCH NEXT FROM QuestionNo INTO @counter
WHILE @@FETCH_STATUS <> -1
select @counter
Begin

--Loop through questions
Declare Questions Cursor For
select QuestionID, Target from Question where CustomSchoolID IN (101, @SchoolID) and QuestionNO = @counter
Open Questions
Fetch NEXT FROM Questions into @QuestionID, @Respondent
WHILE @@FETCH_STATUS <> -1
Begin
if @Respondent = 'P'
Begin
select @parentResult = CAST(AVG(AnswerValue) AS decimal(10,2)) from Answer where QuestionID = @QuestionID and SurveyID = @SurveyID
End
else
if @Respondent = 'S'
Begin
select @studentResult = CAST(AVG(AnswerValue) AS decimal(10,2)) from Answer where QuestionID = @QuestionID and SurveyID = @SurveyID
End
else
if @Respondent = 'F'
Begin
select @staffResult = CAST(AVG(AnswerValue) AS decimal(10,2)) from Answer where QuestionID = @QuestionID and SurveyID = @SurveyID
End
End
CLOSE Questions
DEALLOCATE Questions
-- end Questions Loop
-- Insert my results
Insert resultsPerceptionsSchool (survey_id, question_id, student_result, staff_result, parent_result, total_student, total_staff, total_parent, results_date)
Values (@SurveyID, @QuestionID, @studentResult, @staffResult, @parentResult, 0,0,0,'07/20/2004')

End
CLOSE QuestionNo
DEALLOCATE QuestionNo
-- end QuestionNo Loop

End
CLOSE Surveys
DEALLOCATE Surveys
-- end Survey Loop
return 0
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-17 : 21:11:47

I do not believe this solution requires a cursor. If you post some sample data and expected results someone will be happy to help you write this w/o using cursors.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 21:13:59

It looks like a join between Survey and Questions should get this all in one insert statement with no cursor at all. Need to look at the data of course like ehorn said. You might need a derived table for the second part.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 02:51:56

Probably something like:


CREATE PROCEDURE dbo.sp_CalculateResults
AS
INSERT	dbo.resultsPerceptionsSchool 
(
	survey_id, question_id, student_result, staff_result, parent_result, 
	total_student, total_staff, total_parent, results_date
)
SELECT DISTINCT
	S.SurveyID, 
--	S.SchoolID,
--	[Counter] = Q1.QuestionNO,
	[QuestionID] = Q1.QuestionID, 
--	[Respondent] = Q2.Target,
	[parentResult] = 
			(
				SELECT	CAST(AVG(AParent.AnswerValue) AS decimal(10,2))
				FROM	dbo.Question Q2P
					JOIN dbo.Answer AParent
						 ON AParent.QuestionID = Q2P.QuestionID
						AND AParent.SurveyID = S.SurveyID 
				WHERE	    Q2P.CustomSchoolID IN (101, S.SchoolID)
					AND Q2P.QuestionNO = Q1.QuestionNO -- (Counter)
					AND Q2P.Target = 'P'
			),
	[studentResult] = 
			(
				SELECT	CAST(AVG(AStudent.AnswerValue) AS decimal(10,2))
				FROM	dbo.Question Q2S
					JOIN dbo.Answer AStudent
						 ON AStudent.QuestionID = Q2S.QuestionID
						AND AStudent.SurveyID = S.SurveyID 
				WHERE	    Q2S.CustomSchoolID IN (101, S.SchoolID)
					AND Q2S.QuestionNO = Q1.QuestionNO -- (Counter)
					AND Q2S.Target = 'S'
			),
	[staffResult] = 
			(
				SELECT	CAST(AVG(AStaff.AnswerValue) AS decimal(10,2))
				FROM	dbo.Question Q2F
					JOIN dbo.Answer AStaff
						 ON AStaff.QuestionID = Q2F.QuestionID
						AND AStaff.SurveyID = S.SurveyID 
				WHERE	    Q2F.CustomSchoolID IN (101, S.SchoolID)
					AND Q2F.QuestionNO = Q1.QuestionNO -- (Counter)
					AND Q2F.Target = 'F'
			),
	[total_student] = 0,
	[total_staff] = 0,
	[total_parent] = 0,
	[results_date] = '07/20/2004'
FROM	dbo.Survey S
	JOIN dbo.Question Q1
		 ON Q1.CustomSchoolID IN (101, S.SchoolID)
WHERE 	    S.status = 'C'
GROUP BY S.SurveyID, Q1.QuestionID

(Please note: you should not prefix SProc names with "sp_" because SLQ looks fo them in master first, then your database)

Kristen

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-19 : 11:52:14

Thanks for the prompt responses. I'm in the process of trying out Kristens' solution. Below is each table with some sample data:

When a survey is flagged as completed I wanted to run the procedure to calculate the final results for that survey and dump the data into resultsPerceptionSchool.

We have 32 standard questions that all schools will give to each respondent type (parent, staff, student). Then each school has the ability to create custom questions (CustomSchoolID). The standard 32 questions are flagged with a CustomSchoolID of 101.

Table: Question
QuestionID Question Language Target QuestionNo CategoryID CustomSchoolID
1003 The teachers at my child's school encourage students’ positive behavior. E P 1 1001 101
1035 Teachers at my school encourage students’ positive behavior. E F 1 1001 101
1067 Teachers at my school encourage students’ positive behavior. E S 1 1001 101

Table: Survey
SurveyID SurveyNM Status EnabledYN SchoolID
1000 Sample Survey3 C 1 1000
1004 Sample Survey2 I 1 1000

Table: Answer
AnswerID AnswerValue RespondentType SurveyID QuestionID Gender Grade
2561 1 P 1000 1003 M 0
2496 1 F 1000 1035 F 0
2464 1 S 1000 1067 M 5

Table: resultsPerceptionSchool
id survey_id question_no student_result staff_result parent_result total_student total_staff total_parent results_date
100 1000 1 1.67 2.0 1.5 20 10 10 07/19/2004

thanks for the help!

- Dan

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-19 : 13:12:56

Hello Again -
I've been working with the example provided up above. I'm able to get it working except for one problem with the surveyID reference.

Where is states: AND AParent.SurveyID = S.SurveyID -- if I hard code this to a number to one that exists in the db (1000) I can get results, other wise i'm getting an error:
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.

I'm not sure why this is happening..if i run select Distinct separately SurveyID is coming back with the correct results.

any ideas?

SELECT DISTINCT
S.SurveyID,
S.SchoolID,
Q1.QuestionNO,
Q1.QuestionID,
[parentResult] =
(
SELECT CAST(AVG(AParent.AnswerValue) AS decimal(10,2))
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = Q1.QuestionNO -- (Counter)
AND Q2P.Target = 'P'
)

FROM dbo.Survey S
JOIN dbo.Question Q1
ON Q1.CustomSchoolID IN (101, S.SchoolID)
WHERE S.status = 'C'
GROUP BY S.SchoolID, S.SurveyID, Q1.QuestionNO, Q1.QuestionID

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 13:33:12

"any ideas?"

Yeah, I expect I cocked something up!

Can you post a CREATE TABLE for each table, then I can actually try it here and see what I bust.

(You can do this in Enterprise Manager - right click the Database, choose All Tasks : GENERATE SQL. Then press [Show All], pick the appropriate tables; On the OPTIONS tab pick "Indexes", "PK/Foreign keys" and change "International text" to "Windows text" . That should do it.)

Kristen

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-19 : 15:18:54

Here ya go...and thanks again for the help!

---------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Answer_Survey]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Answer] DROP CONSTRAINT FK_Answer_Survey
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Answer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Answer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Question]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Question]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Survey]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Survey]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[resultsPerceptionSchool]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[resultsPerceptionSchool]
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'

GO

if exists (select * from dbo.sysfulltextcatalogs where name = N'SS_Resources')
exec sp_fulltext_catalog N'SS_Resources', N'drop'

GO

if not exists (select * from dbo.sysfulltextcatalogs where name = N'SS_Resources')
exec sp_fulltext_catalog N'SS_Resources', N'create'

GO

CREATE TABLE [dbo].[Answer] (
[AnswerID] [int] IDENTITY (1000, 1) NOT NULL ,
[AnswerValue] [float] NOT NULL ,
[RespondentType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SurveyID] [int] NOT NULL ,
[QuestionID] [int] NOT NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Grade] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Question] (
[QuestionID] [int] IDENTITY (1, 2000) NOT NULL ,
[Question] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Language] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Target] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QuestionNO] [smallint] NOT NULL ,
[CategoryID] [int] NOT NULL ,
[CustomSchoolID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Survey] (
[SurveyID] [int] IDENTITY (1000, 1) NOT NULL ,
[SurveyNM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EnabledYN] [bit] NOT NULL ,
[SchoolID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[resultsPerceptionSchool] (
[id] [int] IDENTITY (2, 2) NOT NULL ,
[survey_id] [int] NOT NULL ,
[question_no] [int] NOT NULL ,
[student_result] [float] NOT NULL ,
[staff_result] [float] NOT NULL ,
[parent_result] [float] NOT NULL ,
[total_student] [int] NOT NULL ,
[total_staff] [int] NOT NULL ,
[total_parent] [int] NOT NULL ,
[results_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Answer] WITH NOCHECK ADD
CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED
(
[AnswerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Question] WITH NOCHECK ADD
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Survey] WITH NOCHECK ADD
CONSTRAINT [PK_Survey] PRIMARY KEY CLUSTERED
(
[SurveyID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[resultsPerceptionSchool] WITH NOCHECK ADD
CONSTRAINT [PK_resultsPerceptionSchool] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Answer] WITH NOCHECK ADD
CONSTRAINT [DF_Answer_Grade] DEFAULT (0) FOR [Grade]
GO

CREATE INDEX [IX_Answer_RespondentType] ON [dbo].[Answer]([RespondentType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Answer_SurveyID] ON [dbo].[Answer]([SurveyID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Answer_QuestionID] ON [dbo].[Answer]([QuestionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Answer_Result] ON [dbo].[Answer]([SurveyID], [QuestionID], [RespondentType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Question] WITH NOCHECK ADD
CONSTRAINT [DF_Question_Language] DEFAULT ('E') FOR [Language],
CONSTRAINT [DF_Question_QuestionNO] DEFAULT (0) FOR [QuestionNO],
CONSTRAINT [DF_Question_CategoryID] DEFAULT (1006) FOR [CategoryID],
CONSTRAINT [DF_Question_CustomSchoolID] DEFAULT (101) FOR [CustomSchoolID]
GO

CREATE INDEX [IX_Question_CategoryID] ON [dbo].[Question]([CategoryID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Survey] WITH NOCHECK ADD
CONSTRAINT [DF_Survey_EnabledYN] DEFAULT (0) FOR [EnabledYN]
GO

CREATE INDEX [IX_Survey_SchoolID] ON [dbo].[Survey]([SchoolID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Answer] ADD
CONSTRAINT [FK_Answer_Survey] FOREIGN KEY
(
[SurveyID]
) REFERENCES [dbo].[Survey] (
[SurveyID]
)
GO

ALTER TABLE [dbo].[Question] ADD
CONSTRAINT [FK_Question_Category] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category] (
[CategoryID]
)
GO

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 01:24:35

The GROUP BY in what I originall psoted doesn;t cover all the columns it needs to, but I'm not quite sure, logicwise, whether I should just take the GROUP BY out, and leave the DISTINCT to do the work;

or whether the GROUP BY should cover more columns;

or whether the guts of it should be made made into a sub-query and then JOINed with the other data to make the SELECT DISTINCT.

Dunno if that gives you some ideas? I'll have another look at it later

Kristen

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-20 : 12:04:33

That helped thanks. Here's what I ended up using, just need to validate the figures.

--drop PROCEDURE dbo.CalculateResults

CREATE PROCEDURE dbo.CalculateResults
AS
INSERT dbo.resultsPerceptionSchool
(
survey_id, school_id, question_no, student_result, staff_result, parent_result,
total_student, total_staff, total_parent, results_date
)

SELECT DISTINCT
S.SurveyID,
S.SchoolID,
QRef.QuestionNO,
[studentResult] =
(
SELECT CAST(AVG(AParent.AnswerValue) AS decimal(10,2))
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'S'
),
[staffResult] =
(
SELECT CAST(AVG(AParent.AnswerValue) AS decimal(10,2))
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'F'
),
[parentResult] =
(
SELECT CAST(AVG(AParent.AnswerValue) AS decimal(10,2))
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'P'
),
[studentCount] =
(
SELECT Count(AParent.AnswerValue)
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'S'
),
[parentCount] =
(
SELECT Count(AParent.AnswerValue)
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'P'
),
[staffCount] =
(
SELECT Count(AParent.AnswerValue)
FROM dbo.Question Q2P
JOIN dbo.Answer AParent
ON AParent.QuestionID = Q2P.QuestionID
AND AParent.SurveyID = S.SurveyID
WHERE Q2P.CustomSchoolID IN (101, S.SchoolID)
AND Q2P.QuestionNO = QRef.QuestionNO -- (Counter)
AND Q2P.Target = 'F'
),
[resultsDate] = GetDate()
FROM dbo.QuestionRef QRef,
dbo.Survey S

WHERE S.status = 'C'
AND QRef.QuestionNO <> 1000

GO

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 12:44:25

Well, that's changed a bit, but it looks OK. Couple of observations:

You are using "AParent" as the alias name in lots of places, but only in a couple does it actually represent the "Parent" - I suggest a global change of that to "A" or "ANS" or somesuch.

You have named the last three columns [studentCount], [parentCount], [staffCount]. No actual column name is required here, but I find it handy for cross reference / debugging. However, I try to make them the same as the actual columns names of the table being INSERTed into (i.e. continuing the debugging theme). I notice on that score that I called mine [studentResult] where the column is actually "student_result". The columns that the new ones are going into are things like "total_student" so I would use that name for for consistency.

Lastly, is the S.status always 'C'? and QRef.QuestionNO always not equal to 1000? If not I would suggesting passing those as parameters to the CalculateResults SProc.

"just need to validate the figures."

Hope that goes well - obviously with the wholesale changes we've made rigourous testing would be a good idea to make sure we haven't introduced some bugs - perish the thought!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources