July 4, 2009 at 1:04 am
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.
July 4, 2009 at 1:58 am
waleed_m_M (7/4/2009)
select * from (SELECT column_name,column_defaultFROM INFORMATION_SCHEMA.COLUMNS
where table_Name ='TMovie' ) as ____TEMP____TABLE___
set @t=(select column_def from ____TEMP____TABLE___ where column_name='Key')
A derived table only lasts the duration of the query that it's defined in. It's not a temp table or table variable that has scope and existance until it's dropped. All you're doing with AS is naming a subquery for use elsewhere in the query.
If you want to create a temp table for later usage, you need to use the SELECT ... INTO syntax, or create the temp table and use INSERT INTO .. SELECT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2009 at 2:09 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128741
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"
N 56°04'39.16"
E 12°55'05.25"
July 4, 2009 at 9:30 am
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
July 4, 2009 at 9:49 am
Why do you think there is a locking problem?
N 56°04'39.16"
E 12°55'05.25"
July 4, 2009 at 11:10 am
I've read that in this topic:
http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx
July 4, 2009 at 11:24 am
Well, the article is 7 years old, and does enforce some of the SQL Server myths.
TempDB is NOT locked when using temporary tables.
N 56°04'39.16"
E 12°55'05.25"
July 4, 2009 at 11:30 am
waleed_m_M (7/4/2009)
but temp tables have problems in performance since they make locking for the server when it's under construction .
That was a problem on SQL 2000, when there was excessive temp table usage (continual creation and destruction). It is much less of an issue on SQL 2005. Also that article's incorrect in several aspects. The entire of TempDB is not locked and, the locks on the system tables are not full table locks and are only held for the duration of the create table or select into.
Have you tested and confirmed that using Temp Tables causes a performance problem in your case?
I used derived table to enhance performance .
If you use derived tables, you have to do everything in one select statement. Despite the name, derived tables are simply named subqueries and do not persist.
will the Table variable be good for performance?
In general, no, especially on large row sets. Plus they're created in the tempDB database had hence have the same creation overheads (small) as temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2009 at 11:32 am
thanks
are you of this information? is there any reference for this information?
thanks
July 4, 2009 at 11:38 am
Which information in particular to you want a reference to confirm against?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2009 at 12:32 pm
waleed_m_M (7/4/2009)
thanksare you of this information? is there any reference for this information?
thanks
I think that this might be a good read for you: Comparing Table Variables to Temporary Tables[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2009 at 2:28 pm
thanks a lot all
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply