| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-04-09, 04:00
|
|
Registered User
|
|
Join Date: Jun 2009
Posts: 8
|
|
|
"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.
|
|
07-04-09, 04:18
|
|
Registered User
|
|
Join Date: Dec 2007
Location: London, UK
Posts: 732
|
|
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.
|
|
07-04-09, 12:31
|
|
Registered User
|
|
Join Date: Jun 2009
Posts: 8
|
|
|
|
but I've tried that prefix in many other procedures ,and it works
|
|
07-04-09, 12:58
|
|
Registered User
|
|
Join Date: Dec 2007
Location: London, UK
Posts: 732
|
|
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.
|
|
07-04-09, 16:14
|
|
Registered User
|
|
Join Date: Apr 2007
Posts: 183
|
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
"derived table" invalid object name problem
