Dynamically Add columns to a table

Posted on 2005-04-25
Medium Priority
Last Modified: 2010-03-19
I was wondering if anyone can help, I am trying to dynamically construct a temp table in a stored procedure, based on what the user selects determines how many columns the tbale would have, I originally create the table with one column, and then planned to add using ALTER TABLE nested in a WHILE LOOP, the @col variable controls how many columns required. The problem is I was hoping to use the value of @cols as the column name, that is were I come unstuck as it won't let me. This is the way I had envisaged the code :-

CREATE TABLE #RESULTS (Dept varchar(10))

WHILE @cols < 1
      ALTER TABLE #RESULTS ADD @cols float
      SET @cols = @cols - 1

because of security proveledges I cannot create a string and execute as this would drop out of this stored procedure and DBA doesn't allow this

Any help would be appreciated
Question by:bant
1 Comment
LVL 17

Accepted Solution

mokule earned 2000 total points
ID: 13857869
I have a very great solution for You

CREATE TABLE #RESULTS5 (Dept varchar(10))
SET @cols = 5
IF @cols > 0
      ALTER TABLE #RESULTS5 ADD c1 float
IF @cols > 1
      ALTER TABLE #RESULTS5 ADD c2 float
IF @cols > 2
      ALTER TABLE #RESULTS5 ADD c3 float
IF @cols > 3
      ALTER TABLE #RESULTS5 ADD c4 float
IF @cols > 4
      ALTER TABLE #RESULTS5 ADD c5 float
IF @cols > 5
      ALTER TABLE #RESULTS5 ADD c6 float

and so on :)

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question