Dynamically Add columns to a table

Posted on 2005-04-25
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

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now