cursor to alter table, add columns

Friends,
I am trying to use a cursor inside a stored procedure to add columns to a
temp table. Can anyone tell me how to fix the following code so the cursor
will feed new column names to the alter table statement?
The code fails when I try to use a variable as a column name in the alter
table statement.
Thanks for your help ...
DECLARE @.strVar varchar(7)
If object_id('tempdb..#temptbl') is not null
begin
drop table #temptbl
end
Create Table #temptbl
(sku varchar(15) null)
DECLARE mycursor CURSOR
FOR
SELECT colA
FROM PermTable
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVAr varchar(20) null
WHILE @.@.fetch_status = 0
BEGIN
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVar varchar(20) null
END
CLOSE mycursor
DEALLOCATE mycursorWhy in the world would you ever want to do this'''?
nevermind.......
CREATE TABLE #thisisstupid(colname VARCHAR(256))
GO
INSERT #thisisstupid(colname)
SELECT 'col1' UNION ALL
SELECT 'col2' UNION ALL
SELECT 'col3' UNION ALL
SELECT 'col4'
DECLARE @.thisisstupider VARCHAR(4000)
SELECT @.thisisstupider = ''
SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
FROM #thisisstupid
SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupider
SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
EXEC(@.thisisstupider)
SELECT * FROM #thisisstupid
DROP TABLE #thisisstupid
GO
"bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
> Friends,
> I am trying to use a cursor inside a stored procedure to add columns to a
> temp table. Can anyone tell me how to fix the following code so the cursor
> will feed new column names to the alter table statement?
> The code fails when I try to use a variable as a column name in the alter
> table statement.
> Thanks for your help ...
>
> DECLARE @.strVar varchar(7)
> If object_id('tempdb..#temptbl') is not null
> begin
> drop table #temptbl
> end
> Create Table #temptbl
> (sku varchar(15) null)
> DECLARE mycursor CURSOR
> FOR
> SELECT colA
> FROM PermTable
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVAr varchar(20) null
> WHILE @.@.fetch_status = 0
> BEGIN
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVar varchar(20) null
> END
> CLOSE mycursor
> DEALLOCATE mycursor
>|||If you are so disgusted, why even reply? Keep your "stupid" remarks to
yourself.|||You may need to do this when you want the procedure to return a "pivot table
"
- i.e., the values in one column need to become column headers in the
returned records -in the meantime I was able to find a guy at a local compan
y
who is familiar with this technique, using a cursor. It does involve storin
g
the entire ALTER TABLE statement ( + a variable for the new column value)
inside a variable, as you've done below.
Inside each loop the new column value is updated and the sp_executesql is
executed to run the ALTER TABLE string.
Thanks for your reply ...
bill morgan
"Derrick Leggett" wrote:

> Why in the world would you ever want to do this'''?
> nevermind.......
>
> CREATE TABLE #thisisstupid(colname VARCHAR(256))
> GO
> INSERT #thisisstupid(colname)
> SELECT 'col1' UNION ALL
> SELECT 'col2' UNION ALL
> SELECT 'col3' UNION ALL
> SELECT 'col4'
> DECLARE @.thisisstupider VARCHAR(4000)
> SELECT @.thisisstupider = ''
> SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
> FROM #thisisstupid
> SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupide
r
> SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
> EXEC(@.thisisstupider)
> SELECT * FROM #thisisstupid
> DROP TABLE #thisisstupid
> GO
> "bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
> message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
>
>|||Because normally people do this for a stupid reason. :) He actually has an
interesting reason for doing it. Calm down guy. It's not the end of the
world. He took it a little better than you. And, it is stupid that you
would have to do this for a PIVOT table in SQL Server. It's also
unfortunate in 2005 that they haven't fixed this. You still need to
hardcode the values for the columns, which IS STUPID!!!!! And, I won't keep
my stupid remarks to myself. People need to think about what they are
doing.
The MS reason for the pivot table in 2005 being written in that format is
because "it would cause problems with the optimizer otherwise". That really
doesn't cut it. The pivot is a great idea. The way they implemented it
though forces the everyday user to resort to dynamic SQL for it to be truly
useful. That's a shame. They should have a warning in Books Online about
the optimizer and plan generator having some issues with a dynamic comic
list, not just exclude the functionality completely.
"bd" <bryce_dooley123@.yahoo.com> wrote in message
news:1110478752.721077.32400@.o13g2000cwo.googlegroups.com...
> If you are so disgusted, why even reply? Keep your "stupid" remarks to
> yourself.
>sql