SQL Server Performance

dynamically add columns using stored procedure

Discussion in 'General DBA Questions' started by a8le, Apr 10, 2006.

  1. a8le New Member

    hi all,

    i am trying to add a commenting system to my site, and need help dynamically adding columns to the comments table... i google "dynamically add columns" and didn't find any good tutorials..

    can you suggest a good tutorial or help provide example code for me to add columns to my table.

    this i plan on desgining table in this fashion...

    name: CommentsTable
    default columns:

    1) commentID - primary key
    2) blogID - foreign key
    3) comment1 - one default column
    4) comment2 - from here on... if there is going to be more than one comment... i want my stored procedure to add them if neccessary...

    i would appreciate any comments... thanx in advance.

    -a8le
  2. vbkenya New Member

    [Bad]
    Adding columns to a Table should be accomplished with ALTER TABLE ADD < column_definition >.
    Unfortunately you can't use the ALTER TABLE within a stored procedure!

    You would have to use another avenue e.g. A SQL query embedded in your web page.
    End Bad


    [Better]
    You don't need to dynamically add columns to your table. Just use what you have minus the fourth column:

    name: CommentsTable
    default columns:

    1)CommentID -PK
    2)BlogID - FK
    3)Comment - nvarchar()

    As users add comments to a blog, you insert them into the table and each comment gets its own unique commentID and is related to the blog by the BlogID.

    You can still select all comments for a particular blog using the blogID (foreign key) in the WHERE or JOIN clause.





    Nathan H. Omukwenyi
    MVP [SQL Server]
  3. a8le New Member

    oh, duh, i am brain dead toady, thnx for the enlightment. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  4. vbkenya New Member

    Nobody can be declared brain dead until they have typed:

    SELECT * FROM * WHERE *=*





    Nathan H. Omukwenyi
    MVP [SQL Server]
  5. Adriaan New Member

    Hm, just to keep those brain cells alive ... Of course you can use ALTER TABLE in a stored procedure - just as long as it's a temp table!

    You can make good use of this in case you need to do a pivot operation outside of SQL 2005
  6. vbkenya New Member

    Note: You can actually use the ALTER TABLE statement for all types of tables in a stored procedure. Just remember to fully qualify the table name thus [owner.table]

    I actually meant to indicate my reservations for using the statement in the form of a bad design decision. And can you imagine the number of Schema Locks requested and granted on the table everytime users wanted to add more comments than they had done before!

    Nathan H. Omukwenyi
    MVP [SQL Server]
  7. Adriaan New Member

    Nathan, that's why I mentioned temp tables.

    I fully agree that this is not something one should do with permanent tables in a stored procedure, as this will usually be something to make a workaround happen.

Share This Page