Invalid Object name while accessing the temporary table in aprocedure

I am getting the invalid object name  error while trying to execute the below procedure.Please let me know the reason for the error.
alter  procedure test1
as
begin

declare @str varchar(50)
exec('create table #temp(i int) ')

set @str='select * from #temp'
exec(@str)
end

exec test1

SrinivasRaviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paul_p_vargasCommented:
why not just do this instead?

create  procedure test1
as
begin

declare @str varchar(50)
create table #temp(i int)

select * from #temp

end
go
exec test1
0
SrinivasRaviAuthor Commented:
Vargas,

My requirement is to create the temporary table dynamically .So i can't go by a prdefined structure.

Though in my procedure i have defined my attributes ,my actual requiremnet is to create the attributes of the table dynamically.Please advice
0
guveraCommented:
paul p answer seems to be right. No need to use this one exec('create table #temp(i int) '). You can directly execute the query to create temporary table inside the sp. The scope of the temporary table is inside of particular execution.

The same thing happened in the next line set @str='select * from #temp'
exec(@str). So need to declare directly instead of set the variable and execute it.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Umar Topia.Net Full Stack DeveloperCommented:
EXEC has its own scope

if you define a table inside exec, then you can use it inside EXEC only

Please refer:-
http://www.megasolutions.net/Sqlserver/exec%28create-table-26989.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50514


0
SrinivasRaviAuthor Commented:
guvera
As mentioned in my previous post my requirement is to create a temporary table dynamically.The table can have any number of columns which is decided during run time,then insert values into the table and then display the values in the table.
Please find the procedure below.

alter  procedure test1
as
begin

declare @str varchar(50)
declare @a as varchar(100)
--@a is set dynamically
exec('create table #temp('+ @a + ')')

set @str='select * from #temp'
exec(@str)
end

exec test1.

So in this case please advice how can i implement the required functionality.
0
paul_p_vargasCommented:
You have to put alias on your temp table

alter  procedure test1
as
begin

declare @str varchar(50)
set @str= 'create table #temp(i int)'

set @str= @str + 'select * from #temp as Temp'
exec(@str)
end
go
exec test1

Please note that this will return no record as there is nothing being inserted.
Since this is also a GLOBAL temp file, it might affect your performance. consider using Table variable:

alter  procedure test1
as
begin

declare @str varchar(50)
set @str= 'declare @temp as table(i int)'

set @str= @str + 'select * from #temp as Temp'
exec(@str)
end
go
exec test1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
guveraCommented:
try to execute the below sp

alter procedure test1
as
begin


declare @a as varchar(100)
--@a is set dynamically

create table #temp('+ @a + ')

declare @str varchar(50)
select @str = 'ALTER TABLE #tempADD ' + @a

exec(@str ) --- 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
0
guveraCommented:
sorry i miss the space in the query between temp and ADD

select @str = 'ALTER TABLE #temp ADD ' + @a
0
SrinivasRaviAuthor Commented:
paul,Guvera,
Your solutions are acceptable but let me know one thing .Which one would be a better approach to use, table variables or temporary tables.Performance is my major concern.Please advice.
0
SrinivasRaviAuthor Commented:
Thanks for providing the solution
0
paul_p_vargasCommented:
table variables is recommended since it is being stored in the memory
#temp tables are using TempDB, therefore it is using DISK. it also has a global scope, meaning, it can be accessed by other process.
0
SrinivasRaviAuthor Commented:
Paul,
In your previous post also we can create and select only in one exec,but i want the created temporary table  to be active throught out the procedure.I havent checked this in your post properly at that time.Could you please advice.
0
paul_p_vargasCommented:
you have to put all your sql statement inside a string then. that way, they will be in the same scope as the temp table
0
SrinivasRaviAuthor Commented:
Paul,
But i have to insert data into that temporary table by using a cursor which i cannot accomdate in single string.I want a way of creating a temporary table dynamically which can be used across the stored procedure.Is there any way of creating a dynamic temporary table whose scope is across the project.
0
paul_p_vargasCommented:
hi SrinivasRavi,

It would complicate your stored procedure if you use cursor. However, you can still follow this pseudocode:


strSQL = "-- declare temp table "

Cursor Begin
   -- do something else

   strSQL = strSQL + "insert records sql statement"

   -- do something else

End Cursor


exec(strSQL(
0
guveraCommented:
Hi,
Try to execute this sp using cursor., I didnt check it.
create table #temp('+ @a + ')
DECLARE det_cursor CURSOR FOR
declare @str varchar(50)
select @str = 'ALTER TABLE #tempADD ' + @a
OPEN det_cursor
FETCH NEXT FROM det_cursor INTO @str
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec(@str)
END
CLOSE det_cursor
DEALLOCATE det_cursor
0
SrinivasRaviAuthor Commented:
Paul,
What u said is right but i have a requirement of either inserting or updating a record if it exists already in the table  and there are many other condition that i might check before inserting or updating.

So all this cannot be assigned to a sigle string in my opinion, instead if i can create a temporary table  which can be used as a normal table  that will be of great use to me.Let me know if there is any way to do this.The main idea is to use the temporary table at many instance s.So it would not be possible to combine everything and execute it.
Hope you understood the problem.Please advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.