Dynamic fields in SQL

I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

Something like:
create table MY_TABLE (id int, name varchar(32) primary key(id))
create table MY_DYNAMIC_ATTRIBUTES
( id int
, attribute_name varchar(20)
, attribute_type_code char(1)
, attribute_value varchar(255)
primary key (id, attribute_name)
foreign key (id) references MY_TABLE(id)
)

The problem is usually the application reading and writing these tables.
A general report would need crosstabbing.

Specific reports, where you already know which fields are involved is easier, could even be done with a view:

create view MY_DYNAMIC_VIEW as
select
t.id
, t.name
, d1.attribute_value as address
, d2.attribute_value as city
, convert(int, d3.attribute_value) as age
from MY_TABLE t
left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'|||Originally posted by plextoR
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)

Suggestions?

Yeah, don't do it...

Just think what kind of mess you'll end up with...

Ummmm I want a varchar(8000) column...ummm I want another one...and another one...

Booooooooooooooooooooooom

What's business requirement to support, in non tech terms...