Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
My stored procedure is as follows, I have implemented it using stored procedures:
ALTER PROCEDURE VIEW_RESULTS
 
@formID INT
 
AS
DECLARE @questionID INT
DECLARE @applicantID INT
DECLARE @applicantName varchar(MAX)
DECLARE @qID INT
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
 

DECLARE all_applicants_cursor CURSOR FOR 
SELECT applicantID
FROM FormFillers
WHERE formID=@formID
 

DECLARE questions_cursor CURSOR FOR 
SELECT questionID 
FROM FormQuestions
WHERE formID=@formID
 
	
open all_applicants_cursor
FETCH NEXT FROM all_applicants_cursor 
INTO @applicantID
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
		SELECT applicantName + CHAR(13)+CHAR(10) 
		From  RegisteredApplicants
		WHERE applicantID=@applicantID
 
			open questions_cursor
			FETCH NEXT FROM questions_cursor 
			INTO @qID
 
			WHILE @@FETCH_STATUS = 0
			BEGIN
 
				SELECT Q.questionText
				FROM FormQuestions Q, Answers A
				WHERE Q.questionID=@qID AND A.questionID=@qID AND A.applicantID=@applicantID
 
			FETCH NEXT FROM questions_cursor 
			INTO @qID	
    
			END 
			CLOSE questions_cursor;
			DEALLOCATE questions_cursor;
	
FETCH NEXT FROM all_applicants_cursor 
INTO @applicantID	
END 
CLOSE all_applicants_cursor;
DEALLOCATE all_applicants_cursor;
In My asp control(using grid view for experimenting), I need to display the result of this select statement:
SELECT Q.questionText
FROM FormQuestions Q, Answers A
WHERE Q.questionID=@qID AND A.questionID=@qID AND A.applicantID=@applicantID
 
But when I set the data source and test the query, the poped up message says: The query didn't returned any data tables.
 
This is my first attempt with the cursors, kindly assist me in this.
Thanks.
Posted 10-Nov-12 22:35pm
Edited 11-Nov-12 0:27am
(no name)86.4K
v2
Comments
Deepak Subramanian at 11-Nov-12 9:25am
   
May I know why you need cursors in first place? You should be able to implement this with simple join to all the tables.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 210
2 BillWoodruff 205
3 Jochen Arndt 180
4 Sergey Alexandrovich Kryukov 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,650
3 Kornfeld Eliyahu Peter 3,460
4 Sergey Alexandrovich Kryukov 2,811


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 11 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100