SQL Server – How to Access Table Datatype Like an Array

sql serversql-server-2016

I tried to store data in a table variable @coordinates, which contains 2 rows and 2 columns: latitude and longitude.

To store value:

declare @coordinates table(latitude decimal(12,9), longitude decimal(12,9)) 
insert into @coordinates select latitude, longitude from loc.locations where place_name IN ('Delhi', 'Mumbai')

to see the value:

select * from @coordinates

result:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

But I want to access the values one by one like:
@coordinates[1][1] or @coordinates.latitude[1]

or any easy way possible?

Best Answer

While @coordinates is a variable, it is a table variable, not an array. You access data stored in a table variable the same way you would access data in a conventional table, i.e. using a query.

Now when you want to retrieve data of a table's specific row, you need a way of referencing that row. Often you use some sort of an ID column. In this case you could add such column in your @coordinates declaration:

declare @coordinates table
(
  id int identity (1,1),
  latitude decimal(12,9),
  longitude decimal(12,9)
);

The id column is declared as an IDENTITY column and will be populated automatically as rows are added to @coordinates. So now after running your INSERT statement, the contents will look like this:

id  latitude      longitude
--  ------------  ------------
1   28.666670000  77.216670000
2   19.014410000  72.847940000

And in order to retrieve, say, latitude from row 1, you would do

SELECT latitude FROM @coordinates WHERE id = 1;

If necessary, you can use it as a scalar expression where appropriate, for example:

SELECT
  some_columns,
  (SELECT latitude FROM @coordinates WHERE id = 1) + another column AS some_alias
FROM
  some_table
WHERE
   ...

Granted, this is not as pretty as just @coordinates.latitude[1], but it is a conventional way of working with data in SQL.

Note, though, that if you need to retrieve the contents of this table in a query involving other tables and the row ID is going to depend on some table's column, it will be more natural to join @coordinates to the corresponding table:

SELECT
  ...
  c.latitude + t.some_other_column AS whatever
FROM
  some_table AS t
  INNER JOIN @coordinates AS c ON t.some_column = c.id
WHERE
  ...
Related Question