|
"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:
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.
|
|