Cursor alternative, need help - 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

SQL Server Development (2000)

Cursor alternative, need help

Author

Topic

bergkamp
Starting Member

2 Posts

Posted - 2005-05-30 : 04:26:30

Hi guys, hopefully someone can give me a hand with something I am having some problems with.

Firstly let me explain what I need to do. And sorry for the length of this!

I have a form building section for a website I am making, in which allows via a CMS, an administrator create a form,
and then add questions to the form.

On the user side, the form is pulled, with all the questions and drawn so that the user can go through and fill in
an answer for all the questions.

When a user completes the form, i firstly create an entry in a table, which holds the userID, formID, time entered etc.
And for each question that was asked, I insert a record into a answer table, which holds the ID entry table, and also
stores the answer the user typed into a varchar field.

I then need to be able to generate a CSV of all the entries, with the answers provided on the same row, in a field
called "answers". This is where I am having problems.

I have got something to kind of work, which involves;

* creating a temporary table which holds all the entry information, and also pulls out the user details like name etc,
which i just inner join to based on the userID i have in the entry table.
At this point there is also an empty column called answers which contains "" as its value.

* then for each row, i use a cursor that pulls out all the answers for each question for the form, and appends them
to a string i build up. This string is been held in a variable.

* Reset temporary tables answer column to hold info in string variable

* Select everything from temporary table

* Kill table

/* *********************************************************************** */
Sample Code
/* *********************************************************************** */

create table #TemporaryTable ( [entry_id] varchar(25), [entry_date] varchar(11), [member_id] varchar(25), [member_name] varchar(50), [answer] varchar(1) )

insert into #TemporaryTable
select E.entry_id, E.entry_date, E.member_id, M.member_name, ' ' as answer
from ENTRY E
inner join MEMBER M on E.member_id = M.member_id
where E.form_id = 1
order by E.entry_date

alter table #TemporaryTable
alter column [answer] text

declare @answer varchar(8000),@answerStr varchar(8000),@fetch_entry_id int,@fetch_entry_id_previous int,@fetchAnswer varchar(8000)
set @answer = ''
set @answerStr = ''
set @fetch_entry_id = 0

declare c1 cursor read_only for

select EA.entry_id, ('Q' + CAST(Q.question_order AS varchar(3)) + ':' + EA.entry_answer_text) AS Answer
from ENTRY_ANSWER EA
inner join QUESTION Q ON EA.question_id = Q.question_id
where Q.form_id = 34
order by EA.entry_id

open c1 fetch next from c1 into @h, @fetchAnswer SET @fetch_entry_id_previous = @fetch_entry_id
while @@FETCH_STATUS = 0 begin
IF @fetch_entry_id <> @fetch_entry_id_previous begin
update #TemporaryTable
set answer = @answerStr
where entry_id = CAST(@fetch_entry_id_previous AS int)
SET @answerStr = ''
end
set @answerStr = @answerStr + @fetchAnswer + '. '
set @fetch_entry_id_previous = @fetch_entry_id fetch next from c1 into @fetch_entry_id, @fetchAnswer
end

update #TemporaryTable
set answer = @answerStr
where entryID = cast(@fetch_entry_id_previous AS int)

close c1 deallocate c1

select entry_id, entry_date, member_id, member_name, answer
from #TemporaryTable

drop table #TemporaryTable

/* *********************************************************************** */
Sample Data
/* *********************************************************************** */

entry_id ,entry_date ,member_id ,member_name ,answer
1 ,1/1/2000 ,1 ,"Bob" , "Q1:Hello. Q2:Goodbye. Q3:Hi"
2 ,2/1/2000 ,2 ,"Frank" , "Q1:Hello. Q2:Goodbye"
3 ,3/1/2000 ,3 ,"Anna" , "Q1:Hello. Q2:Goodbye. Q3:Hi. Q4:Bye"

/* *********************************************************************** */
Conclusion
/* *********************************************************************** */

This obviously is rubish, it takes along time (for 30,000+ rows), hammers the
server etc. What is a better way to pull multiple items from another table, and
then return it as a column inside another select.

Thanks + Please help

/* *********************************************************************** */

bergkamp
Starting Member

2 Posts

Posted - 2005-05-30 : 21:05:29

bump

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-31 : 01:11:58

Como?

HTH

=================================================================
The surest way to corrupt a youth is to instruct him to hold in higher esteem those who think alike than those who think differently. -Friedrich Nietzsche, philosopher (1844-1900)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources