Solved

Invalid Object name while accessing the temporary table in aprocedure

Posted on 2010-11-08
18
921 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:SrinivasRavi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 5
  • +1
18 Comments
 
LVL 3

Expert Comment

by:paul_p_vargas
ID: 34090522
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
 

Author Comment

by:SrinivasRavi
ID: 34090529
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
 
LVL 8

Expert Comment

by:guvera
ID: 34090545
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 10

Expert Comment

by:Umar Topia
ID: 34090567
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
 

Author Comment

by:SrinivasRavi
ID: 34090571
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
 
LVL 3

Accepted Solution

by:
paul_p_vargas earned 167 total points
ID: 34090582
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
 
LVL 8

Assisted Solution

by:guvera
guvera earned 333 total points
ID: 34090607
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
 
LVL 8

Expert Comment

by:guvera
ID: 34090615
sorry i miss the space in the query between temp and ADD

select @str = 'ALTER TABLE #temp ADD ' + @a
0
 

Author Comment

by:SrinivasRavi
ID: 34090627
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
 
LVL 8

Assisted Solution

by:guvera
guvera earned 333 total points
ID: 34090641
0
 

Author Closing Comment

by:SrinivasRavi
ID: 34090651
Thanks for providing the solution
0
 
LVL 3

Expert Comment

by:paul_p_vargas
ID: 34090672
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
 

Author Comment

by:SrinivasRavi
ID: 34091495
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
 
LVL 3

Expert Comment

by:paul_p_vargas
ID: 34091510
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
 

Author Comment

by:SrinivasRavi
ID: 34091541
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
 
LVL 3

Expert Comment

by:paul_p_vargas
ID: 34091558
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
 
LVL 8

Expert Comment

by:guvera
ID: 34091604
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
 

Author Comment

by:SrinivasRavi
ID: 34091608
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question