Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: help with the SP

    I am trying to get the oldquestionID and the newQuestionID and then insert into a table. Insert into survey and SurveyQuestions is working fine.

    DECLARE @NewID INT
    declare @oldquestionID int
    declare @newquestionID 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. If i do the select statement below it only shows 1 result. Actually there can be 2 or 3 or more rows for questionID. SO how can i loop thru one by one get the oldquestionid, then newquestionID and then do an insert for each questionid.

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

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

    Here i am using the insert statement using the value form the @oldquestionID and @newquestionID


    INSERT Surveychoices(QuestionId,choice)
    SELECT @newquestionID,choice
    FROM Surveychoices
    WHERE questionid=@oldquestionID

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is why we don't use SELECT @var = ... before we understand it's behaviour.

    If you run the query without the variable in it, I bet you get more than one row, right?
    Code:
    select QuestionId from surveyquestions where surveyid='81' and answertype <> 'T'
    You see, the problem you have is that a scalar variable can only hold a single scalar value. Your query returns more than one result, so one of these values is arbitrarily assigned to your variable to give you a value; not so cool eh?

    Well how do we fix this? Well the first thing to point out is that the standard way to assign variable values is to actually use the SET keyword, e.g.
    Code:
    SET @var = 1
    SET only allows a single value assignment. As such the following statement will fail if the query returns more than one record
    Code:
    SET @var = (select QuestionId from surveyquestions where surveyid='81' and answertype <> 'T')
    A common way around this is to change your query to only return one result. Perhaps you only want the "top" result; in which case Max(QuestionId) will do.

    However it sounds like because you have more than one record being returned, you want more than one value back to use later on...

    A great way to achieve this is to change your scalar variable in to a table variable!
    Code:
    DECLARE @var table (
       col_a int
    )
    
    INSERT INTO @var (col_a)
    select QuestionId from surveyquestions where surveyid='81' and answertype <> 'T'
    Hope this helps
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    So how can i insert the rows one by one into Surveychoices.

    INSERT Surveychoices(QuestionId,choice)
    SELECT @newquestionID,choice
    FROM Surveychoices
    WHERE questionid=@oldquestionID
    Last edited by coolaggie; 08-03-10 at 19:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •