May 6, 2003 at 3:35 pm
I am trying to convert a stored procedure to use the new data type table instead of a temptable. I am getting an error when I EXEC the sql string.
If I do this:
Select @sql='INSERT @t_table1 SELECT i.ImageID, i.DisplayImageName, i.ImagePath,i.restrictioncode, i.RollOverCaption, BiasFactor=kj.BiasFactor* i.AgeFactor*i.MiscFactor, TotalFactor = i.AgeFactor*i.MiscFactor, i.CreatedDate, i.PG ,wordcount=1,InLightBox=0,InCart=0, i.IsRF,i.Similar,i.OnCD ,i.IsFileCodeShot,i.Orientation,ImageHitCount=1 FROM tblImageMain i , tblKeyJoin kj , tblKeyword k ' + @tablecollection + ' WHERE i.ImageID = kj.ImageID AND kj.KeywordID = k.KeywordID ' + @ImageClause + @timestring + @colstring + ' AND i.Similar<>1 AND k.KeywordVerbose LIKE ''' + @keystring + ''' AND i.ShowOnWeb = 1' + @RFClause + ' order by BiasFactor desc'
EXEC @sql
I get a 'table not declared' error
If however, I just use the following it works fine.
INSERT @t_table1 SELECT DISTINCT i.ImageID, i.DisplayImageName, i.ImagePath,i.restrictioncode, i.RollOverCaption, BiasFactor=kj.BiasFactor* i.AgeFactor*i.MiscFactor, TotalFactor = i.AgeFactor*i.MiscFactor, i.CreatedDate, i.PG ,wordcount=1,InLightBox=0,InCart=0, i.IsRF, i.Similar,i.OnCD ,i.IsFileCodeShot,i.Orientation,ImageHitCount=1
FROM tblImageMain i , tblKeyJoin kj , tblKeyword k WHERE i.ImageID = kj.ImageID AND kj.KeywordID = k.KeywordID AND i.Similar<>1 AND k.KeywordVerbose LIKE 'dog' AND i.ShowOnWeb = 1 order by BiasFactor desc
I'm sure this is easy but I don't understand why the 'EXEC' doesn't work when it worked ok using a temp table.
TIA,
John
May 7, 2003 at 7:15 am
I believe you are going to need to declare the table variable & fields before you can reference it. Not like a temp table that you can delcare and populate on the fly.
declare @table table
(
1st field integer,
2nd field varchar(20),
etc
)
May 7, 2003 at 7:26 am
I think I found the problem. Seems you cannot use EXEC to work with the data type Table.
I've tried simple examples and it works fine without using EXEC but whenever I try to use EXEC it fails...
Oh well...
Edited by - jmccary on 05/07/2003 07:27:15 AM
Edited by - jmccary on 05/07/2003 07:27:27 AM
May 16, 2003 at 12:55 am
EXEC (dynamic) is always executed in a separate scope, which means it does not have the variables that were declared in the scope where "exec" appears.
Just as
declare @code1 varchar(10)
execute 'select @code1=1'
doesnt work, nether will
declare @tbl1 table
execute 'select 1 as one into @tbl1'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply