| Author |
 Topic  |
|
|
srinath
Starting Member
16 Posts |
 Posted - 11/06/2006 : 08:02:19
|
Hi All,
I am trying to create the temporary table dynamically in the stored procedure as
Exec ('Create table #TEMP(' + @A + ')
I have the values in the variable @A = 'Period1 varchar(25),Period2 varchar(25),Period3 varchar(25),Period4 varchar(25),Period5 varchar(25))
But when I tried to query the table...Its giving an error Invalid object...
I don't want to have the global temporary table and also I don't want to create the table as it may cause us some problems if the same SP is used my more than one person.
Is there any way that we create the temp table in the SP and use that in the same SP.
Thanks in Advance.
|
Edited by - srinath on 11/06/2006 08:03:35
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 11/06/2006 : 08:08:32
|
you have to use global temp table. since the scope of the temp table wont allow you to access the table.
read on the book online for the scope of the temp or hash tables, you will get better idea.
what are you trying to do, may be someone over here may give a better option to do it?
Chirag
http://chirikworld.blogspot.com/ |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/06/2006 : 08:14:39
|
Either use global temp tables (##tables) as Chirag said or combine your query command inside the same exec call like this:
exec ('create table #x( a int) insert into #x values(1) select * from #x')
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
samuelclay
Yak Posting Veteran
USA
71 Posts |
Posted - 11/06/2006 : 14:45:12
|
Good article.
The temp table doesnt need to be global to be seen by the procedure in and out of dynamic code, just created before the dynamic code is executed. The table will be accessible in the dynamic code and after it has completed running.. the scope of the temp table will prevent any data problems from having multiple users running the procedure (or even the same user running it multiple times at once). Maybe for the OP's specific needs, just create the temp table with an identity then use dynamic sql to add the columns..
DECLARE @cols varchar(2000) --- this could be an imput parameter
select @cols = 'Period1 varchar(25),Period2 varchar(25),Period3 varchar(25),Period4 varchar(25),Period5 varchar(25)'
CREATE TABLE [#test2]
( [t2_Key] [int] IDENTITY(1,1) NOT NULL )
declare @sql varchar(2000)
select @sql = 'ALTER TABLE #test2 ADD ' + @cols
exec(@sql) --- this adds the fields to the temp table
The table can now have data added and selected from, by both direct queries and dynamic sql.. |
|
|
|
srinath
Starting Member
16 Posts |
Posted - 11/09/2006 : 03:42:17
|
As I was busy on some other priority things I didn't get a chance to look at the query, today I did the same way as giveb by samuelclay and it is working ..
Thanks a lot samuelclay & spirit1
|
|
|
|
pelegk2
Aged Yak Warrior
Israel
720 Posts |
Posted - 03/31/2008 : 06:58:48
|
i have tried samuel way :
set @DynamicSql=' ALTER TABLE #ReportTable ADD date_'+@ReportMonthStart+' bigint'
where i have created at first the table #ReportTable .
i got this error :
quote:
Could not find stored procedure ' ALTER TABLE #ReportTable ADD date_200709 bigint'.
any idea why?
or any other way to do this?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 03/31/2008 : 07:01:36
|
Missing parenthesis after EXEC, huh?
exec(@sql)
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
pelegk2
Aged Yak Warrior
Israel
720 Posts |
Posted - 03/31/2008 : 07:27:37
|
yes
it was the problem oopps
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
jnich76
Starting Member
USA
1 Posts |
Posted - 09/28/2012 : 10:33:22
|
You can use the global temp table to dynamically create the table without declaring it beforehand.
set @sql = 'Select col1, col2 into ##tempF from sometable where 1=1'
exec(@sql)
Select * from ##tempF
WORKS! |
|
|
| |
Topic |
|
Creating temp table dynamically
