Post reply

how to write for loop and cursor in sql server

  • March 16, 2012 at 6:42 am

    #253293

    for example in my store proc i mention a temp table

    in that i am getting result of a select query

    example

    value(column name)

    ram

    bala

    gowtham

    by using cursor

    i just want to fetch the value

    one by one

    in a another where condition

  • March 16, 2012 at 6:50 am

    #1460461

    Why do you want to fetch the values one by one instead of as a set? What are you going to do with them?

    John

  • March 16, 2012 at 6:55 am

    #1460463

    Why do you need to use a cursor? There is nothing in your post that indicates that one is needed.

    I will be honest, if you really think you need to use one, here is how you create one:

    declare the cursor (look this up in Books Online)

    open the cursor (look this up in Books Online)

    fetch the first value (or set of values) into a variable (or set of variables) (again, look this up in Books Online)

    while @@fetch_status = 0 (your initial fetch was successful, and again you can find more in Books Online)

    begin

    do something (what ever it is you think you must do row by agonizing row)

    fetch next value or values (as you did for the initial fetch)

    end

    close the cursor (forgot this, look it up in Books Online)

    deallocate the cursor (forgot this too, look it up in Books Online)

    Of course, what ever you are doing above could probably be done better using a set-based solution.

    Edit: Forgot two important steps.

  • March 16, 2012 at 6:55 am

    #1460464

    iam just using that value one by one for searching

    a name in a another table iam using like %+@value+%

  • March 16, 2012 at 7:01 am

    #1460473

    You probably need to read about JOINs. No need for row-by-row processing, and much quicker.

    John

  • March 16, 2012 at 7:03 am

    #1460474

    er.sivaganesh (3/16/2012)

    iam just using that value one by one for searching

    a name in a another table iam using like %+@value+%

    I figured. Your other posts weren't giving you the answer you wanted so you decided to try again.

    You really need to go back to those other posts and answer the questions we have asked of you. Post the information we requested so we can show you a better way to accomplish the task you are attempting to solve without having to use cursors.

  • March 16, 2012 at 7:19 am

    #1460489

    no i don't nead join query

  • March 16, 2012 at 7:29 am

    #1460492

    er.sivaganesh (3/16/2012)

    no i don't nead join query

    And more importantly, YOU DON'T NEED A CURSOR EITHER.

  • March 16, 2012 at 7:54 am

    #1460515

    this is starting to sound like homework to me. Any one else?

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • March 16, 2012 at 8:16 am

    #1460534

    capn.hector (3/16/2012)

    this is starting to sound like homework to me. Any one else?

    Maybe. If I were teaching a class on T-SQL/MS SQL Server I would give any solution to a homework assignment an F (0) if it used a cursor. I wouldn't even cover it in class or test on it. It the students wanted to learn how to write a cursor, they can learn it on their own.

    Problem here is an complete unwillingness to provide the requested information (won't read and follow the instructions in the first article I reference in my sig block). Won't except the knowledge and experience of numerous individuals on this site, that combined is probalby over 100 years with MS SQL Server.

  • March 16, 2012 at 1:43 pm

    #1460742

    thanks for every one who joined in this discussion i just solved this problem by creating cusor in my store procedure

  • March 16, 2012 at 1:45 pm

    #1460744

    er.sivaganesh (3/16/2012)

    thanks for every one who joined in this discussion i just solved this problem by creating cusor in my store procedure

    Forum etiquette would have you post your solution.

  • March 16, 2012 at 2:37 pm

    #1460780

    Lynn Pettis (3/16/2012)

    er.sivaganesh (3/16/2012)

    thanks for every one who joined in this discussion i just solved this problem by creating cusor in my store procedure

    Forum etiquette would have you post your solution.

    One could argue that adding a cursor to the stored proc is not as much a solution as it is a temporary work around. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 16, 2012 at 11:42 pm

    #1460831

    er.sivaganesh (3/16/2012)

    thanks for every one who joined in this discussion i just solved this problem by creating cusor in my store procedure

    Gosh... after all the warnings about cursors and you still used one. I hope, for your employer's or customer's sake, it's truly something that requires a cursor instead of what you said you were going to use it for.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 17, 2012 at 12:40 am

    #1460836

    Jeff Moden (3/16/2012)

    er.sivaganesh (3/16/2012)

    thanks for every one who joined in this discussion i just solved this problem by creating cusor in my store procedure

    Gosh... after all the warnings about cursors and you still used one. I hope, for your employer's or customer's sake, it's truly something that requires a cursor instead of what you said you were going to use it for.

    This link below will give you an idea of why he wanted a CURSOR

    http://www.sqlservercentral.com/Forums/Topic1267085-391-1.aspx

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply