How can my web users name new columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can my web users name new columns

Author

Topic

smithje
Starting Member

32 Posts

Posted - 2002-01-04 : 16:53:57

I need to add new columns to a table using a column name input by a user from a web page. I get an error using a variable in a stored procedure with the Alter Table statement and Books Online informs me that sub queries are not allowed in the Alter Table statement. How can I create a new column and assign a column name entered by the user? We need to track compliance on activities and allow a controller to add new ones for tracking.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-04 : 17:10:19

This sounds like a job for Dynamic SQL. There are a couple great articles on this site showing how to use it. Do a SEARCH for dynamic SQL to find them.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-01-04 : 17:14:13

You can use dynamic SQL and do something like this:

declare @column_name varchar(255), @strSQL varchar(100)
set @column_name='test'
set @strSQL='ALTER TABLE testTable ADD '+@column_name+''+' varchar(10)'
exec (@strSql)

PS Do not forget to grant your user(s) right to alter a table

helena

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-01-04 : 17:31:18

Having answered your question, I decided to give you an advice. Adding new columns to your table doesn’t sound like a good idea.

A better approach will be to create another table to store all those activities such as
Activity_id
Activity_description
Date_created
Created_by

Let your users first populate table 1 and add an activity. Then have them populate your main table. (Add one column to your main table – activity_id).

helena

smithje
Starting Member

32 Posts

Posted - 2002-01-04 : 17:50:40

Thanks Helena. So far I have gotten your example to work by making a stored procedure with a variable and setting your variable "column_name" to the value of my new variable. I am puzzled why I cannot just pass a value for "column_name" but as usual I will take the workaround. I will consider the advice next.
[/quote]

[/quote]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-04 : 17:56:53

The problem is that ALTER TABLE statements (and the like) need to be explicit in every detail. You're not dealing with data values in this instance, but actual table/index/database structures. (think about a car: changing the color vs. changing the number of wheels) Dynamic SQL lets you work around this limitation by creating the entire statement as a variable, then executing it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources