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

Question

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

masangombali2

SSC Enthusiast

Points: 181
More actions
October 16, 2020 at 8:47 pm

#3798154

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.

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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.

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

ScottPletcher SSC Guru Points: 100947 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

masangombali2 SSC Enthusiast Points: 181 More actions · Answer 3

@jeff Moden -- Thank you for the guidance.

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

frederico_fonseca SSCoach Points: 16014 More actions · Answer 4

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: 1003863 More actions · Answer 5

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.

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

masangombali2 SSC Enthusiast Points: 181 More actions · Answer 6

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: 27038 More actions · Answer 7

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/