SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Creating temp table dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srinath
Starting Member

16 Posts

Posted - 11/06/2006 :  08:02:19  Show Profile  Reply with Quote
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  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/06/2006 :  08:14:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/06/2006 :  08:39:45  Show Profile  Visit spirit1's Homepage  Reply with Quote
to understand temp tables:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Edited by - spirit1 on 11/06/2006 08:39:56
Go to Top of Page

samuelclay
Yak Posting Veteran

USA
71 Posts

Posted - 11/06/2006 :  14:45:12  Show Profile  Visit samuelclay's Homepage  Reply with Quote
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..
Go to Top of Page

srinath
Starting Member

16 Posts

Posted - 11/09/2006 :  03:42:17  Show Profile  Reply with Quote
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
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 03/31/2008 :  06:58:48  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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 -:)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 03/31/2008 :  07:01:10  Show Profile  Visit spirit1's Homepage  Reply with Quote
you'll have to show us more sql than that...


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 03/31/2008 :  07:01:36  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Missing parenthesis after EXEC, huh?

exec(@sql)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 03/31/2008 :  07:27:37  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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 -:)
Go to Top of Page

jnich76
Starting Member

USA
1 Posts

Posted - 09/28/2012 :  10:33:22  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000