help with this SP with cursor - 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 2005 Forums

Transact-SQL (2005)

help with this SP with cursor

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2010-08-04 : 14:19:55

I am trying to get the questionID from two different Select statements from the same table and then insert into a different table one by one.

DECLARE @NewID INT

This one is inserting a new surveyID, Output is SurveyID

insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()

Copying the questions with the new surveyID

INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'

The problem is below here. I want to get the value of the questionId of the first select statement then the value of the questionID of the second select statement and insert into the table SurveyChoices one by one.
Both the select statements can have 1 or 2 or 3 rows or more but the both the select statements will have exact number of rows.

select QuestionId from surveyquestions where surveyid=@NewID and answertype <> 'T'

select QuestionId from surveyquestions where surveyid='81' and answertype <> 'T'

Here i am using the insert statement using the value form First select
statement and second select statement

INSERT Surveychoices(QuestionId,choice)
SELECT questionID,choice((This is the value of the First select statement.)
FROM Surveychoices
WHERE questionid=questionID(This is the value of the second select statement.)

I tried to do this way but i am not getting the results

DECLARE @NewID INT
declare @QuestionId INT

insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()

INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'
Declare OldQuestion CURSOR FAST_FORWARD FOR

select QuestionId from surveyquestions where surveyid='81' and answertype <> 'T'

OPEN OldQuestion
FETCH NEXT FROM OldQuestion INTO @QuestionId
PRINT 'OUTER LOOP START'
WHILE @@FETCH_STATUS = 0
BEGIN

Declare NewQuestion CURSOR FOR
select QuestionId from surveyquestions where surveyid=@NewID and answertype <> 'T'
--select @questionID as newquestionid

OPEN NewQuestion
FETCH NEXT FROM NewQuestion INTO @QuestionId
PRINT 'Inner LOOP START'
WHILE @@FETCH_STATUS = 0
BEGIN
select @questionID as newquestionid
select @questionID as oldquestionid

INSERT Surveychoices(QuestionId,choice)
SELECT @questionid,choice --This questionID will come from the second select statement
FROM Surveychoices
WHERE questionid=@questionid--This questionID will come from the first select statement

FETCH NEXT FROM NewQuestion
INTO @QuestionId
PRINT 'Inner LOOP'
End
CLOSE NewQuestion
DEALLOCATE NewQuestion

FETCH NEXT FROM OldQuestion
INTO @QuestionId
PRINT 'OUTER LOOP'
End

CLOSE OldQuestion
DEALLOCATE OldQuestion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources