Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: "derived table" invalid object name problem

    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;
    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.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    but I've tried that prefix in many other procedures ,and it works

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    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.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •