dynamic column creation

[kavinhuh]

hello , i have a doubt , see my requirement is to add a new column to an existing table such tat the column name should be automatically given the name of the variable . i wrote the query as

alter table test add'+@count+' int

, but i am getting error any idea how to solve this , waiting for your reply

[India777]

What error you got?

Please Put the Space between add and '+@count+'.

[kavinhuh]

Incorrect syntax near '+@count+'

.
this is the error which i got .

[India777]

Please send the function Coding you wrote to alter the Table.

[Rabbit]

I have one question. Why? It sounds to me like you're breaking the rules of normalization but I can't be sure until I know the reason you need to do this. But I strongly suspect that is the case, in which case, don't do it, you're just making it more difficult for yourself down the line.

[ck9663]

You have to build a string for the whole statement and execute the string using EXEC. Here read some of this...

Happy Coding!!!


~~ CK

[kavinhuh]

well , in my case i have to insert the url of the image inserted by the user into the db , so i wanted to create a new column every time a user inserts a new url . i think it ll make things easier for me while retrieving the value of url .

[kavinhuh]

i even tried usig exec but still getting the same error , i don't think it ll make any difference in coding

[Rabbit]

You're definitely breaking the rules of normalization. Every time the user adds a new URL, insert a new row, don't create a new column every time. You should have a URL table and just keep adding rows to that. What you're trying to do now will only make querying a nightmare later on.

[kavinhuh]

well , my full requirement is like i ll add a new image and edit its attributes like width, height , once this is done , i ll add that image url as column and attributes as column values , then get its attributes by using select 'image url' from the table ... do u have any idea how to do this

[Rabbit]

You create a table with a field for the URL and all your attributes. Then you have one row per image.