Accession Columns Names as a Array 1

[hanglam]

Hi,

I have a table with the following columns:

Table1:
PersonID, [1], [2], [3],][4] , [5] and so on .

The TABLE FIELD NAMES are actual numbers (not letters) representing the days of a month.

I'mm writing a stored procedure to populate this table with UPDATE Statement (NOT INSERT statements as the PersonID is already in the table)

is there any way for me to access the column as a array ?

For example:

UPDATE Table1 Set column[4] = 'hello' where PersonID = 1


I tried this:

declare @index varchar(2)
set @index = '3' (column with the name '3')

UPDATE Table1 Set @index = 'hello' where PersonID = 1

but it doesn't work.



Thanks,
Hang

 

[gmmastros]

The only way I can think of it to use dynamic SQL.

Declare @SQL VarChar(8000)
Declare @i Integer

Set @i = 1
While @i < 32
  Begin
    Set @SQL = 'Update Table1 Set [' + Convert(VarChar(2), @i) + '] = ''Hello'' Where PersonId = ' + Convert(Varchar(20), @PersonId)

    Exec (@SQL)

    Set @i = @i + 1
  End

Instead of doing this, I recommend you create a table to store this info, following database normalization rules. You will save yourself a lot of headaches later.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom