Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: 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....

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to use object owner (object_owner.table_name)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Instead of :

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

    you could use exec:

    exec('insert into ...'+ @FieldName+') ...'

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    OK, thanks, I'll give that a go...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can turn the whole thing in to a set based solution..

    Also, are you sure that the query will return 1 row...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Smile

    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...

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    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 :-)

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mmcdonald
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •