Post reply

relative position of field in a recordset

  • July 25, 2013 at 9:58 am

    #280956

    I need to know the value in a field at a variable offset, or relative position, in a cursor. (I know, a cursor.)

    In VBA I would open a recordset and use rst.Fields(i), where i is the variable offset value.

    I need to run an UPDATE against a temp table based on the value in the field:

    UPDATE tmptankbooking SET field(i) = @LocationAbbr

    I'd rather not be doing this in a cursor, but I'm not experienced enough to think of an alternative.

    Thanks.

  • July 25, 2013 at 10:21 am

    #1635821

    In T-SQL, you cannot refer to "a field" in a table by its "index".

    However, you can use dynamic sql o build a relevant update statement using table definition details from sys.columns table.

    Here is an idea how it may work:

    SELECT @colname = name FROM sys.columns WHERE object_id = object_id('yourtablename') and columnid = @i

    SET @SQL = 'UPDATE yourtablename SET ' + @colname + ' = whatevervalue'

    EXEC (@SQL)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 25, 2013 at 11:50 am

    #1635856

    Thank you. That's what I needed.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply