(derived table) invalid object name problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

(derived table) invalid object name problem

Author

Topic

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-07-04 : 02:55:26

hi
I've mad a stored procedure that uses derived table to insert params into a table name TMovie but after settting null params to their defaults,I use the derived table to store columns default values.The same technique worked with other tables with 2 or 3 columns ,but in this table that has many columns a strange message appeared to me when calling the stored procedure:

Msg 208, Level 16, State 1, Procedure sp_TMovie_Insert, Line 64
Invalid object name '____TEMP____TABLE___'.

(1 row(s) affected)

(1 row(s) affected)

here is stored procedure code code;


PROCEDURE [dbo].[sp_TMovie_Insert]
@Key uniqueidentifier , 
@Name varchar (100) , 
@Genre varchar (50) , 
@ReleaseWindow varchar (50) , 
@PosterImage image = null , 
@Actors varchar (200) , 
@Synopsis varchar (500) , 
@Rating varchar (50) , 
@ReleaseDate varchar (50) , 
@RunningTime varchar (50) , 
@TStamp datetime , 
@DateCreated datetime , 
@CreatedBy varchar (50) , 
@LastUpdatedBy varchar (50) = null , 
@Source varchar (50) , 
@ErrorCode int OUTPUT

AS

SET NOCOUNT ON

         
          select * from (SELECT column_name,column_default

          FROM INFORMATION_SCHEMA.COLUMNS
          where table_Name ='TMovie' ) as ____TEMP____TABLE___

          declare @t nvarchar(4000) --temporary variable       
          
          if @Key is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Key')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Key = convert ( uniqueidentifier,@t)
          end
          
          
          if @Name is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Name')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Name = convert ( varchar (100),@t)
          end
          
          
          if @Genre is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Genre')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Genre = convert ( varchar (50),@t)
          end
          
          
          if @ReleaseWindow is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='ReleaseWindow')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @ReleaseWindow = convert ( varchar (50),@t)
          end
          
          
          if @PosterImage is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='PosterImage')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @PosterImage = convert ( varbinary(max),@t)
          end
          
          
          if @Actors is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Actors')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Actors = convert ( varchar (200),@t)
          end
          
          
          if @Synopsis is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Synopsis')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Synopsis = convert ( varchar (500),@t)
          end
          
          
          if @Rating is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Rating')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Rating = convert ( varchar (50),@t)
          end
          
          
          if @ReleaseDate is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='ReleaseDate')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @ReleaseDate = convert ( varchar (50),@t)
          end
          
          
          if @RunningTime is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='RunningTime')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @RunningTime = convert ( varchar (50),@t)
          end
          
          
          if @TStamp is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='TStamp')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @TStamp = convert ( datetime,@t)
          end
          
          
          if @DateCreated is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='DateCreated')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @DateCreated = convert ( datetime,@t)
          end
          
          
          if @CreatedBy is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='CreatedBy')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @CreatedBy = convert ( varchar (50),@t)
          end
          
          
          if @LastUpdatedBy is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='LastUpdatedBy')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @LastUpdatedBy = convert ( varchar (50),@t)
          end
          
          
          if @Source is null
          begin
            set @t=(select column_def from ____TEMP____TABLE___ where column_name='Source')
            set @t = replace (@t ,'(','')
            set @t = replace (@t ,')','')
            
            set @Source = convert ( varchar (50),@t)
          end
          
            


          -- INSERT a new row in the table
INSERT INTO [dbo].[TMovie]( [Key],[Name],[Genre],[ReleaseWindow],[PosterImage],[Actors],[Synopsis],[Rating],[ReleaseDate],[RunningTime],[TStamp],[DateCreated],[CreatedBy],[LastUpdatedBy],[Source] )
VALUES ( @Key,@Name,@Genre,@ReleaseWindow,@PosterImage,@Actors,@Synopsis,@Rating,@ReleaseDate,@RunningTime,@TStamp,@DateCreated,@CreatedBy,@LastUpdatedBy,@Source )

-- Get the Error Code for the statment just executed
SET @ErrorCode = @@ERROR

and here's the calling statments:


DECLARE	@return_value int,
		@ErrorCode int

EXEC	@return_value = [dbo].[sp_TMovie_Insert]
		@Key = NULL,
		@Name = N'eee',
		@Genre = N'eeee',
		@ReleaseWindow = N'erere',
		@PosterImage = NULL,
		@Actors = NULL,
		@Synopsis = N'sdfsdfdsf',
		@Rating = N'33',
		@ReleaseDate = N'11111',
		@RunningTime = N'dfdfdf',
		@TStamp = NULL,
		@DateCreated = NULL,
		@CreatedBy = NULL,
		@LastUpdatedBy = NULL,
		@Source = NULL,
		@ErrorCode = @ErrorCode OUTPUT

SELECT	@ErrorCode as N'@ErrorCode'

SELECT	'Return Value' = @return_value

GO

why the message of "invalid object name" appears to me? is this a known limitation in derived tables ?

thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 03:02:08

select *
into #TempTable
from (SELECT column_name,column_default

FROM INFORMATION_SCHEMA.COLUMNS
where table_Name ='TMovie' )

and change all other occurancies of "____TEMP____TABLE___" to "#TempTable"

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-07-04 : 13:30:58

thanks all
but temp tables have problems in performance since they make locking for the server when it's under construction . I used derived table to enhance performance . will the Table variable be good for performance?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 14:27:15

No. Are you still referring to the 7 year old article? Which also has some fundamental errors in it?

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources