"derived table" invalid object name problem

waleed_cs2000 · #1 (**permalink**) 07-04-09, 03:00

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:

Quote:

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;

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:

Code:

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.

dportas · #2 (**permalink**) 07-04-09, 03:18

There's nowhere in your proc that you actually create that table. Did you intend to do this:

SELECT COLUMN_NAME, COLUMN_DEFAULT AS column_def
INTO dbo.[____TEMP____TABLE___]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TMovie' ;

Never use the prefix "sp_" for user stored procedures. sp_ is reserved for system procedures only and will adversely affect the behaviour of your code.

waleed_cs2000 · #3 (**permalink**) 07-04-09, 11:31

but I've tried that prefix in many other procedures ,and it works

dportas · #4 (**permalink**) 07-04-09, 11:58

It may appear to work but it causes unwanted recompilations, adversely affects performance and can be unreliable. Also it is poor practice because you should use a naming convention that doesn't conflict with Microsoft's recommendations.

"We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures."
CREATE PROCEDURE (Transact-SQL)

The only time you should prefix a stored procedure name with sp_ is if you specifically want to create a system stored procedure in the Master database and with the system proc behaviour.

Peso · #5 (**permalink**) 07-04-09, 15:14

This is also answered
here SQL Server Forums - (derived table) invalid object name problem
and here "derived table" invalid object name

The background is that OP read a 7 year old article which has a number of errors in it.