Have any idea about to add column by stored procedure?

Question

I want to add column to table by stored procedure and the name of column should be parameter.'s value.

Answer 1

You'll have to compose a dynamic DDL SQL statement where you concatenate the name of the column received as argument:

CREATE PROCEDURE AddColumnToTable
    @columnName VARCHAR(128)
AS
EXEC ('ALTER TABLE tableName ADD' + SPACE(1) + @columnName + SPACE(1) + 'VARCHAR(MAX) NULL')

Note that in this example the name of the table as well as the column's type are hard-coded in the SQL statement. You may want to consider adding them as parameters to the stored procedure for a more generic solution.

Related resources:

• Execute (Transact-SQL)
• The Curse and Blessings of Dynamic SQL

Answer 2

This is a terrible idea in general. If your schema is requiring changes to tables that need to be done by sp, then they are happening too frequently and the design should be reviewed.

But if you are stuck with this horrible process (and I can't emphasize enough what a truly bad idea it is.) then you also need to have an input pvalue for the data type for the data as well and a nullable one for the size of the data type if need be, varchar(max) is a poor choice for every possible column to be added. It should only be used when you expect to have more than 8000 characters in the column for indexing reasons.

Why is this bad? Well to begin with, you have lost control over the schena. People can anything and there is no review to make sure you don't get twelve versions of the same things with slighly differnt names added. Next how do you intend to fix the application to use these fieldss without knowing what was added? Since you should never return more fields than you need, your production code should not be using select *. Therefore how do you know which fields to add if your users have added them. Users in general aren't knowledgeable enough to add fields. They don't understand database structure or design and don't understand how to normalize and performance tune. Letting people willy-nilly add fields to data tables is short-sighted and will lead to badly performing databases and awkward, hard-to-use interfaces that annoy the customers. If you have properly done your work in designing the database and application, there should be very little that a user needs to add. If you are basing your work on the user being able to have the flexibilty to add, you have a disaster of a project that not only will be hard to maintain, it will perform badly and in general your users will hate it. I've been forced to work work with some of these horrible commercial products (look at Clarity if you want an example of how flexibilty trumped design and made a product that is virtually unuseable).