Post reply

Table data type

  • May 6, 2003 at 3:35 pm

    #80884

    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

    #456386

    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

    #456389

    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

    #457417

    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