Click here to Skip to main content
12,755,986 members (37,449 online)

How to display the result of a stored procedure (implemented using cursors)

WajihaAhmed asked:

Open original thread
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.
Tags: Cursor, stored-procedure

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web01 | 2.8.170217.1 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2017
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