Help - Any SQL Server gurus out there

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

eurodat

Thread Starter
Joined
Sep 13, 2003
Messages
21
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

Thread Starter
Joined
Sep 13, 2003
Messages
21
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
 
Joined
Jul 29, 2001
Messages
21,334
Are you sure the date/time being inserted is the correct format?
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top