[eurodat]

I have been trying for days to find a way to refer to table field names in TSQL

I have a table CFlowDefs which is simply 200 smalldatetime fields named DT_1 to DT_200
I have a loop which is intended to insert incremental dates into each of the required fields starting from DT_1, then DT_2 etc as far as needed (defined by @NumDays). All I want to do is be able to refer to the required field in CFlowDefs that needs updating using a string (@NextField )

set @counter = 2
while @counter < @NumDays +2
begin
set @StrCounter = CAST(@counter as varchar)
set @NextField = 'DT_' + @StrCounter <-- String to create next field name
set @NextDate = dateadd(d,1,@NextDate)

update CFlowDefs
set @NextField = @NextDate --<---- HELP !!!

set @counter = @counter + 1
end

any help would be very much appreciated

 

[eurodat]

Almost there. For some reason the EXEC statement below doesn't work. SQL doesn't seem to like the @Nextdate part and gives the following errors.... I assume its the way @Nextdate is being interpreted in the EXEC ?

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '15'.
DT_3
Jan 16 2007 12:00AM
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '16'.
DT_4
Jan 17 2007 12:00AM

declare @NextDate smalldatetime
declare @counter int
declare @ColName as sysname
declare @TblName as sysname

SELECT @TblName = 'CFLowDefs'
set @NextDate = '14/jan/2007'
set @counter = 3

while @counter < 5
begin
set @NextDate = dateadd(d,1,@NextDate)
SELECT @ColName = COL_NAME(OBJECT_ID(@TblName), @counter)
print @ColName
print @nextdate
EXEC ('UPDATE ' + @TblName + ' SET ' + @ColName + ' = ' + @Nextdate)

set @counter = @counter + 1
end

 

[Rockn]

Are you sure the date/time being inserted is the correct format?