Solved

Invalid Object name while accessing the temporary table in aprocedure

Posted on 2010-11-08
18
881 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
  • 7
  • 5
  • 5
  • +1
18 Comments
 
LVL 3

Expert Comment

by:paul_p_vargas
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:Umar Topia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sorry i miss the space in the query between temp and ADD

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

Author Comment

by:SrinivasRavi
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Assisted Solution

by:guvera
guvera earned 333 total points
Comment Utility
0
 

Author Closing Comment

by:SrinivasRavi
Comment Utility
Thanks for providing the solution
0
 
LVL 3

Expert Comment

by:paul_p_vargas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now