Table Variable Update error

Question

Table Variable Update error

fperkins

Ten Centuries

Points: 1029
More actions
July 7, 2006 at 10:54 am

#113712

Sorry about the whitespace. Can't seem to get this out of double space mode. (fixed per response below - thanks)
Getting an error on the update statement.
Error: Must declare the variable '@TempTables'
But it is declared and all the other statements insert and select from it so I don’t think it is out of scope. Last select returns all the rows in the table variable.

@sqlexe generates as: Update @TempTables Set counter = (Select count(*) From AA_UATNANCY2BUILD3 Where prospectid = 7736093) WHERE id = 1

Declare     @prospectid     int,
            @tablename      VarChar(80),
            @sqlexe         VarChar(250),
            @loopCounter    int,
            @rowCount       int

set @prospectid = 7736093     -- this is a value in one of the temp tables on my local

Begin

DECLARE @TempTables TABLE(id int identity, tableName varchar(50), campaignId int, filterId int, counter int)
INSERT INTO @TempTables
Select tablename, campaignid, filterid, 0
From CampaignFilterAssignOvr

set @loopCounter = 0
set @rowCount = (select max(id) from @TempTables)

while @loopCounter < @rowCount
     begin
       set @loopCounter = @loopCounter + 1
       set @tablename = (select tableName from @TempTables where id = @loopCounter)

       select @sqlexe = 'Update @TempTables '
       select @sqlexe = @sqlexe + 'Set counter = '
       select @SQLExe = @sqlexe + '(Select count(*) From ' + @tablename + ' Where prospectid = ' + Convert(VarChar(8),@prospectid) + ') '
       select @SQLExe = @sqlexe + ' WHERE id = ' + Convert(VarChar(3),@loopCounter)

       exec (@sqlexe)

     end
End

Select tr.tablename, c.name, c.description, f.name, f.description
From @TempTables tr, Campaign c, filters f
Where tr.campaignid *= c.campaignid
and tr.filterid *= f.filterid
and tr.counter >= 0   -- drop the = if get problem figured out to ensure only those tables with row return

GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

When you use Dynamic SQL, you generate a new SPID and it cannot see the @TableVariable you have created... You may either have to use a physical table or create the table within your Dynamic SQL.

I wasn't born stupid - I had to study.

fperkins Ten Centuries Points: 1029 More actions · Answer 2

Ok, thank you for the info. Changed to a temp table and it works just fine.

stax68 SSChampion Points: 11711 More actions · Answer 3

Using dynamic SQL doesn't generate a new spid (session). It generates a new batch. Temp tables have session scope so they are visible to the new batch. Table variables have batch scope so they aren't.

re: spacing. Highlight the text and select 'Definition Term' from the Style dropdown. If it won't let you, change style to 'Normal' first, then change to 'Definition Term'.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant