SQL Server Parameter Table Values inside SQL query

Question

I made a function that takes a varchar (IP address) and returns table values (city, country, time_zone...)

ALTER function [dbo].[GQuery2](@ipAddress varchar(16)) 
 returns @t table (
    [country_name] [nvarchar](64) ,
    [region_name] [nvarchar](128) ,
    [city_name] [nvarchar](128),
    [latitude] [float],
    [longitude] [float] ,
    [zip_code] [nvarchar](30) ,
    [time_zone] [nvarchar](8) 
 )
 as 
 begin 
    insert into @t ([country_name]
      ,[region_name]
      ,[city_name]
      ,[latitude]
      ,[longitude]
      ,[zip_code]
      ,[time_zone])
 select  [country_name]
      ,[region_name]
      ,[city_name]
      ,[latitude]
      ,[longitude]
      ,[zip_code]
      ,[time_zone]
 from [pcap].[dbo].ip2loc 
 where ip_from = ( 
        select max(ip_from) 
            from [pcap].[dbo].ip2loc 
            where ip_from <= [dbo].IP2INT(@ipAddress) 
            ) 
 and ip_to = ( 
        select min(ip_to) 
            from [pcap].[dbo].ip2loc 
            where ip_to >= [dbo].IP2INT(@ipAddress) 
                ) 
    RETURN  
 end

The problem is I can only call the function in this format:

 SELECT * FROM [dbo].GQuery2('89.98.0.0')

and it returns a new table

country_name    region_name city_name   latitude    longitude   zip_code    time_zone
NETHERLANDS NOORD-HOLLAND   AMSTERDAM   52.37403    4.88969 1089    +02:00

And I want to make a query like this (theoretically)

UPDATE DimLocation d,  [dbo].GQuery2(d.ip) g 
SET d.city_name = g.city_name, d.country_name = g.country_name ....

The DimLocation table has the schema:

IP, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone

The IP address has a value and the other columns are NULL and i need to pass the IP address from DimLocation to the function GQuery2(ip) to get the missing data

The question is how to work with table-valued functions in select statements?

score 0 · Answer 1 · answered Jul 07 '14 at 03:48

0

Try Following query:

Update DimLocation
Set City_Name = g.City_Name,
    Country_Name = g.Country_Name,
    ....
From dbo.GQuery2(DimLocation.IP) g

answered Jul 07 '14 at 03:48

mehdi lotfi

11,194
18
82
128

nothing Update pcap.dbo.DimLocation Set city_name = g.city_name From dbo.GQuery2(d.ip) g :( The multi-part identifier "d.ip" could not be bound. – teter Jul 07 '14 at 18:19

score 0 · Answer 2 · answered Jul 07 '14 at 04:09

Your function should contain IP field also to join the function with DimLocation table otherwise the query will update all the records in DimLocation table Or you have to update single record at a time (ie. Where DimLocation.IP='192.168.0.1'). But, It is better to do practice that retrieved records should have a field that you can match with the parent table which you want to update.

If you are adding the IP field in your function then the query should be written like this.

Update DimLocation Set DimLocation.City_Name = f.City_Name, DimLocation.Country_Name=f.Country_Name,.. 
From DimLocation Inner Join dbo.GQuery2(DimLocation.IP) f On DimLocation.IP=f.IP

Thus, you can update all values in the DimLocation table with your created function in a single query.

SQL Server Parameter Table Values inside SQL query

2 Answers2