1

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?

  • 1
    SQL Server doesn't have ARRAY type. – McNets Jan 19 '18 at 9:18
  • Ya it doesn't have so I used table datatype, but I want to know if any easy way is there to access the column and row one by one? – Gour Gopal Jan 19 '18 at 9:21
  • 1
    Maybe you've chosen the wrong tool for what you want to do? If you want access rows one by one, use any procedural language, you don't need RDBMS – sepupic Jan 19 '18 at 9:29
5

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
  ...
  • thanks. but i think the code SELECT latitude FROM @coordinates WHERE id = 1 is going to hit database – Gour Gopal Jan 19 '18 at 10:12
  • 1
    Yes, under the covers a table variable is implemented as a local temporary table, so storing data into it as well as reading from it is going to hit the tempdb database. – Andriy M Jan 19 '18 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.