August 24, 2012 at 9:05 am
hello.
here is the scenario:
sp 1 inserts data in #table, #table is in sp2 and sp3
sp 2 and sp3 are datasources for 2 report dataregions.
sp 1-> sp2
Sp 1->sp3
both sp2 and sp3 have #temp table. I want to make ## global and use it with both sp2 and sp3 without using it twice.
Wondering if you could advise with?
if I should create ##table in Sp2 and do the insert but how do i use it in sp3 i mean reference wise.
thank you !!
August 24, 2012 at 9:15 am
Will sp2 ever be run while the table already exists? As in, while another copy of sp2 is running, or sp3 is running, or any other data is accessing the table?
If so, you'll need to either reconsider the design (probably your best bet), or make the table creation conditional, or make the proc wait and loop a check on the table, so it doesn't try to create a table that already exists.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2012 at 9:24 am
thanks for replying. I did not understand what you mean.
August 24, 2012 at 9:27 am
If you create a globabl temporary table, one that starts with ## instead of just #, and you have a stored procedure that creates that table, then if the procedure is run while the table already exists, the procedure will fail.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2012 at 9:35 am
oh. I am now thinking off making changes in sp1 adding ## table in there and then using that in sp2 and sp3.
how do i reference those in sp2 and sp3? i mean sharing of global table among sp.
thanks.
August 24, 2012 at 9:55 am
Same as any other table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2012 at 10:03 am
Just make sure that your sp1 is called by one caller/user at the same time...
August 27, 2012 at 5:33 am
GSquared (8/24/2012)
Same as any other table.
and should i drop the ## table in sp2 and sp3 both?
August 27, 2012 at 5:51 am
Eugene Elutin (8/24/2012)
Just make sure that your sp1 is called by one caller/user at the same time...
thank you.
August 27, 2012 at 1:28 pm
SQL_path (8/27/2012)
GSquared (8/24/2012)
Same as any other table.and should i drop the ## table in sp2 and sp3 both?
Generally the good practice is to drop objects within the same procedure where they've been created.
Of course, there are exclusions to every rule.
_____________
Code for TallyGenerator
August 28, 2012 at 4:23 am
Sergiy (8/27/2012)
SQL_path (8/27/2012)
GSquared (8/24/2012)
Same as any other table.and should i drop the ## table in sp2 and sp3 both?
Generally the good practice is to drop objects within the same procedure where they've been created.
Of course, there are exclusions to every rule.
It's really depends on what you want from ## (global temp table), but it's definitely not a good idea for # ones (local temp tables). Actually it is quite an opposite.
Due to their scope, local temp tables are dropped automatically when stored procedures are finished, the dropping them explicitly at the end of proc will give no benefits, but may cause another recompilation...
Sometimes, in ETL processes, you would create some permanent "work" tables on fly.
Even there, in my opinion, it would be better practice to check their existence and drop them (if required) at the beginning of proc. That again may save some recompilations and will represent a bit more robust design.
August 28, 2012 at 1:56 pm
SQL_path (8/24/2012)
hello.here is the scenario:
sp 1 inserts data in #table, #table is in sp2 and sp3
sp 2 and sp3 are datasources for 2 report dataregions.
sp 1-> sp2
Sp 1->sp3
both sp2 and sp3 have #temp table. I want to make ## global and use it with both sp2 and sp3 without using it twice.
Wondering if you could advise with?
if I should create ##table in Sp2 and do the insert but how do i use it in sp3 i mean reference wise.
thank you !!
I'm not sure I quite understand your requirements, but will sp1 execute sp2 and sp3 as nested procedure calls (i.e., will the definition of sp1 include "execute sp2" or "execute sp3")? If so, you can just create and populate the local temporary table (#table) in sp1 and refer to it in sp2 and sp3. A local temporary table created in one stored procedure will be in scope for any nested stored procedures executed by the stored procedure that created the local temporary table.
Jason Wolfkill
August 29, 2012 at 11:39 am
Thank you all.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply