Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raky
Aged Yak Warrior
767 Posts |
 Posted - 2009-05-31 : 09:24:19
|
| Hi,I have a stored procedure in which i dynamically build some columns.When it tried to insert the result of dynamic Query into a temp Table ( Using INTO Operator) the temp table is not accessible outside the dynamic code. How can i access the temp table outside the dynamic code without Predifining the Temp Table structure. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-31 : 09:54:11
|
why can't you pre-create the temp table outside of the dynamic sql ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-05-31 : 10:50:36
|
| Because the columns that returned from Dynamic query are not fixed. We have media table. In that records are like thisMediaID MediaName1 EST2 VOD3 PayTVAnd we have another table Schedule ScheduleID MediaID LicenseStartDate LicenseEnddate1 1 '5/2/2009' '5/31/2009'2 2 '3/4/2009' '6/21/2009'3 3 '1/4/2009' '4/11/2009'From Dyanamic Query We will Show columns EstStartDate, EstEndDate, VODStartDate,VODEndDate,PayTvStartDate, PayTvEndDate ( Buiding Dynamically these columns based on Media Table Values )In Future Records May be Added in Media Table so i cant predefine Temp Table...Hope iam clear.. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-31 : 11:01:24
|
use global temp table
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-05-31 : 12:02:34
|
| Thanks alot Khtan, My problem solved. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-31 : 14:10:18
|
quote:
Originally posted by raky
Thanks alot Khtan, My problem solved.
Hope your procedure wont be executed simultaneously by more than 1 user in which case global temporary table might be of problem. |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-06-01 : 02:01:32
|
quote:
Originally posted by visakh16
quote:
Originally posted by raky
Thanks alot Khtan, My problem solved.
Hope your procedure wont be executed simultaneously by more than 1 user in which case global temporary table might be of problem.
My application is Web Application...Means my procedure may be executed simultaneously by more than 1 user at a time...Then what is solution to my problem. Please Advice...Actually i used global temporary table because i need further calculations from values retrieved from dynamic sql code. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-01 : 06:00:43
|
| you can do 2 things:1) take out the temp table entirely -- just build a dynamic SELECT statement (use a dynamic CTE / derived table if you have to)2) Make a local temp table outside the dynamic block with 1 column (an Id or similar) and then inside your dynamic block use ALTER TABLE statements to add columns to it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Dynamic sp problem