Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Friday, June 22, 2012 7:07 PM
Someone else created this Stored Procedure and I've worn out my welcome on asking anymore questions of him and it time I start boning up on my SQL skills, but the way he wrote this is just so far above my pay grade I can't figure it out.
I added a new column 'RecordID' to the table .IPAdrressTMP so I could keep track of what record I was actually looking at and I'd like this Column to be outputted with the results so I can compare the results to the original. I'm just a research, this isn't being used for a web site or anything.
I've tried adding 'RecordID' to a couple places but keep getting Invalid Column Name 'RecordID'. I've tried changing the name, but I believe the syntax isn't correct.
Thank you for your help.
Jose
Here's the Stored Procedure
USE [maxmindgeolite]
GO
/****** Object: StoredProcedure [dbo].[Lookup_geoip_Multiple] Script Date: 06/22/2012 11:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Lookup_geoip_Multiple]
as select a2.IPAdrress, b.* from(
select dbo.IP_Numeric(a.IPAdrress) as IP, a.IPAdrress from dbo.IPAdrressTMP as a ) as a2
cross apply tvf_Lookup_geoip(a2.IP) as b
Heres the Table-value Funtion it calls
USE [maxmindgeolite]
GO
/****** Object: UserDefinedFunction [dbo].[tvf_Lookup_geoip] Script Date: 06/22/2012 12:03:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[tvf_Lookup_geoip]
(
@IPNumber bigint
)
RETURNS TABLE
AS
RETURN
(
SELECT
Geoloc.country,
Geoloc.region,
Geoloc.city,
Geoloc.latitude,
Geoloc.longitude
FROM GeoLook
INNER JOIN Geoloc ON GeoLook.locId = Geoloc.locId
WHERE (@IPNumber BETWEEN GeoLook.startIpNum AND GeoLook.endIpNum)
)
All replies (2)
Friday, June 22, 2012 7:14 PM âś…Answered
If you have already added a column RecordId to the table IPAdrressTMP, then you can directly include your RecordId in the select on the derived table as follows:
USE [maxmindgeolite]
GO
/****** Object: StoredProcedure [dbo].[Lookup_geoip_Multiple] Script Date: 06/22/2012 11:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Lookup_geoip_Multiple]
as select a2.RecordId,a2.IPAdrress, b.* from(
select a.RecordId, dbo.IP_Numeric(a.IPAdrress) as IP, a.IPAdrress from dbo.IPAdrressTMP as a ) as a2
cross apply tvf_Lookup_geoip(a2.IP) as b
Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.
Friday, June 22, 2012 7:25 PM
Wow! Thank you so much! Works perfectly!
OK. I think I understand. The tables are being alias, not the fields per say? I thought the alias a2 was column specific, so I was putting a3. That went nowhere, fast!
Sincerely,
Jose