Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    42

    Angry Unanswered: Procedure loop problem

    when i run following query through a stored procedure passing parameter nam e_id

    select @state_id =state_id from state where name_id=@name_id
    I get 3 state_id as return value
    all this 3 value i have to insert in a table like this 3 times
    insert into name value (1,@state_id)


    so as to create 3 row for that name table

    How to do that

    Regards
    Bala

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You can can create a stored procedure that uses a cursor for that.
    Use the cursor to process the resultset of the query "select state_id from state where name_id=@name_id", and insert each row in the table "name".
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Sep 2005
    Posts
    42
    Thanks Martijnvs,

    Any other way than cursor

    Regards
    Bala

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by bala_m
    Thanks Martijnvs,

    Any other way than cursor

    Regards
    Bala
    A select into-statement works, but then you'd insert the recors in a new table. Adding the records isn't possible (although I'm not entirely sure about that)

    Is there a reason not to use a cursor?
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Aug 2004
    Posts
    42
    Something like...

    INSERT name
    SELECT ..., state_id, ...
    FROM state
    WHERE name_id = @name_id

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Tad Hawkins
    Something like...

    INSERT name
    SELECT ..., state_id, ...
    FROM state
    WHERE name_id = @name_id
    Right! I completely overlooked this one. Works like a charm, and is dead simple .
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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