[dba-SQLServer] Altering temporary Tables

David Emerson newsgrps at dalyn.co.nz
Wed Sep 12 21:28:24 CDT 2007


I have created a temporary table called @ttmpBWSGoalCashFlow in a 
stored procedure.

I would like to add columns to the table based on the data in another table.

Part of my stored procedure looks like this (Assume that cc has been 
declared as a cursor which returns the names of the new columns):

         DECLARE @col varchar(100)
         DECLARE @strsql varchar(4000)
         OPEN cc
         FETCH NEXT FROM cc INTO @Col
         WHILE @@fetch_status=0
         BEGIN
                 SELECT @strsql = 'ALTER TABLE @ttmpBWSGoalCashFlow 
add [' + @col + '] money'
                 EXEC (@strsql)
                 FETCH NEXT FROM cc INTO @Col
         END

The error I get when I run this is "Incorrect syntax near 
'@ttmpBWSGoalCashFlow'".  My guess is that because the temporary 
table is a variable, SQL can't recognise it as part of the 
string.  Can anyone help me with the correct syntax?




More information about the dba-SQLServer mailing list