[dba-SQLServer] Altering temporary Tables

David,

Instead of using a table variable, can you use a temp table
(#ttmpBWSGoalCashFlow) instead? We use something similar here to create a
server side crosstab

Tom

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: 13 September 2007 03:28
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer] Altering temporary Tables

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?

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com