using same name for cursors and temp tables on different stored procedures

  • masangombali2

    SSC Enthusiast

    Points: 131

    Good day, I need some advice about cursors:

    SP1                                                SP2                                                                  SP3

    Temp tables (3)                           Temp tables (3)                                         Temp tables (3)

    Batch_cursor                                Batch_cursor                                            Batch_cursor

    Line_cursor                                   Line_cursor                                              Line_cursor

    Export_cursor                               Export_cursor                                        Export_cursor

    Export data to a SP1_FINAL  Export data to a SP2_FINAL                 Export data to a SP3_FINAL

    Is it possible

    ·         All the cursors in different SP’s to have the same name.

    ·         Can the temp tables have same names as well?

    ·         Can they all run at the same time without interfering with each other

    What is advisable in these situations.

     

  • Jeff Moden

    SSC Guru

    Points: 997328

    With the idea of teaching a person to fish, a temp table is still a table and you're trying to create one in each of the 3 procs.  That's the hint you need to find the MS documentation on the subject.  So, do a search for the following...

    CREATE TABLE (Transact-SQL)

    The first item returned in the search will probably be the MS documentation on the subject.  If it's not, it probably won't be far from the top.

    Once found, click on the link to get to the MS article (documentation) on the subject.  It's a pretty long article so press [Ctrl][F] to do a find in the browser and search for "Temporary Table" (without the quotes) and click whatever your browser recognizes for the "next" instance of what it found until you get to the section about temporary tables.  The information you seek, along with a whole lot more about temporary tables, is there.

    For your cursor question, do similar.

    Make sure that you read about the differences between "local" and "global" versions of the objects in each of the two articles because the answer to both of your questions actually is "It Depends". 😉

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • ScottPletcher

    SSC Guru

    Points: 98626

    Yes, yes and yes, as long as the proc not being EXEC'd by each other.

    That is, this should work fine:

    --ok

    EXEC sp1

    EXEC sp2

    EXEC sp3

    because SQL will clear temp tables created within a proc when that proc ends.

    But you would have problems if you tried to do this:

    --will fail

    EXEC sp1

    --and inside sp1 it tries to exec sp2:

    CREATE sp1

    AS

    EXEC sp2

    because the temp table that sp1 created is still there when sp2 is called, so trying to create the same table again will fail.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • masangombali2

    SSC Enthusiast

    Points: 131

    @jeff Moden -- Thank you for the guidance.

    @scottpletcher -- Thank you. I am planning to use the first method.

  • frederico_fonseca

    SSChampion

    Points: 14779

    and look at not using cursors at all - in majority of cases where developers think they need cursors that is not the case

  • Jeff Moden

    SSC Guru

    Points: 997328

    To Frederico's good point, please see the following article for an introduction to "Tally Tables" (also known as "Numbers" tables).  Keep in mind that it's only an introduction and do a search for both types of tables for many extraordinary examples of what they can be used to solve for without Cursors, While loop, and Incremental rCTEs (Recursive CTEs), which are all forms of "RBAR" (an acronym for Row By Agonizing Row and is pronounce as "ree-bar"... like the steel rods buried in cement that aren't going anywhere quickly).

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • masangombali2

    SSC Enthusiast

    Points: 131

    Thank you. I will do some research and see how I can achieve the same results without cursors. Because of tight deadlines I have used cursors in achieving what is required and I was a bit worried since I have used same names across the SP's.

  • Michael L John

    One Orange Chip

    Points: 25965

    masangombali2 wrote:

    Thank you. I will do some research and see how I can achieve the same results without cursors. Because of tight deadlines I have used cursors in achieving what is required and I was a bit worried since I have used same names across the SP's.

     

    When there is a tight deadline, and you take a shortcut to meet that deadline, do you realize the potential for creating far far more work than you saved?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 8 posts - 1 through 8 (of 8 total)

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