Append Counter variable to field name

Hi all,

I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!

Here's the proc:

WHILE @.Counter < 4
BEGIN

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @.ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @.DateFrom) AND (tbl_SurgerySlot.StartTime < @.DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)

SET @.FieldName = 'Days' + CONVERT(VARCHAR(20),@.Counter)

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

SET @.DateTo = DATEADD(Day,1,@.DateTo)

--Increment the loop counter
SET @.Counter = @.Counter + 1

When I run the above the follwoing message is displayed:

Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.

The object IS valid so I'm lost...Try to use object owner (object_owner.table_name)|||Instead of :

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

you could use exec:

exec('insert into ...'+ @.FieldName+') ...'|||OK, thanks, I'll give that a go...|||You can turn the whole thing in to a set based solution..

Also, are you sure that the query will return 1 row...|||Hi Bret,

Not sure what u mean by a Set based solution. I've just realised that the way I'm doing this won't work anyway cos' every time I use the INSERT statement it will obviously insert a new row, which I don't want it to do. I want the code to fill up the row with firgures for each day e.g.

Row1 5, 25,6

At the mo' it will do:

Row1 5,
Row2 ,25,
Row3 , , 6

Thought I could maybe store the data in an Array before committing it to the DB but have found T-SQL doesn't support this! Any ideas...|||The are no arrays in sql server...

I guess you could call a table like an array...

If you have sql server 2000 you can use table varialbles...

And I'm kinda of lost (so what else in new) with your example

Can you tell us, in business terms, what you're trying to do?|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)

Thanks for the chuckle...

Lots of time sql server will through an erroneous error...

BUT...your process needs to be changed...

If you ever figure out what's suppose to happen, tell use and post the ddl of the tables, some sample data with dml statements and expected results..

good luck...