July 21, 2008 at 3:47 pm
Hello all, I have this sp that is being used to generate an number of agents by distance from the inputted zip code. It worked fine until i had to apply some filters (by state TX, LA, FL, and all of them combined) which first determines the state then returns results or an error message. When I execute the sp depending on the inputted zip sometimes it returns results, and sometimes even if the zip is in the table, it returns an error message pointing to line175, the LA IF statement. There are only two BEGIN and END Statements that are significant.
IF @Locations > 0 BEGIN and IF EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode) BEGIN. I tried moving the END statement ending the second BEGIN statement above and did not get anywhere.I am pretty sure i have them in the right place. Please take a look and see what I am doing wrong because I cannot see the problem. Thanks
ALTER PROCEDURE [dbo].[spGetAgentsCloseToZipCode]
@NumAgents INT,
@ZipCode VARCHAR(5),
@MaxDistance NUMERIC(18,6)
AS
DECLARE @Latitude NUMERIC(18,6)
DECLARE @Longitude NUMERIC(18,6)
DECLARE @Locations INT
DECLARE @ErrMsg VARCHAR(MAX)
DECLARE @ScenarioID INT
SET @Latitude = 26.0
SET @Longitude = -80.0
Set @ZipCode = RTRIM(LTRIM(CAST(@ZipCode AS VARCHAR(5))))
-- Get ZipCode Lat/Long
SELECT
@Locations = COUNT(*)
FROM
ZipCodes
WHERE
ZipCode = @ZipCode
AND
(StateAbbr = 'TX'
OR
StateAbbr = 'LA'
OR
StateAbbr = 'FL')
IF @Locations > 0
BEGIN
SELECT
@Latitude = Latitude,
@Longitude = Longitude
FROM
ZipCodes
WHERE
ZipCode = @ZipCode
AND
(StateAbbr = 'FL'
OR
StateAbbr = 'TX')
CREATE TABLE #temp_Agents
(
TempAgentId INT IDENTITY(1,1) NOT NULL,
AgentId INT NOT NULL,
HomeWiseAgentID VARCHAR(50) NOT NULL,
AgentName VARCHAR(50) NOT NULL,
ContactName VARCHAR(50) NOT NULL,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
City VARCHAR(100),
State VARCHAR(2),
ZipCode VARCHAR(5),
PhoneNumber VARCHAR(15),
FaxNumber VARCHAR(15),
EmailAddress VARCHAR(100),
Latitude NUMERIC(18,6),
Longitude NUMERIC(18,6),
Distance NUMERIC(18,6),
ziplat NUMERIC(18,6),
ziplong NUMERIC(18,6)
)
INSERT INTO #temp_Agents
(
AgentId,
HomeWiseAgentId,
AgentName,
ContactName,
Address1,
Address2,
City,
State,
ZipCode,
PhoneNumber,
FaxNumber,
EmailAddress,
Latitude,
Longitude,
Distance,
ziplat,
ziplong
)
-- Only SELECT TOP 100 to keep speed, if result set can be >100, increase TOP
SELECT TOP 100
A.AgentId,
A.HomeWiseAgentId,
A.AgentName,
A.ContactName,
LEFT(L.Address1,100),
LEFT(L.Address2,100),
LEFT(L.City,100),
LEFT(UPPER(L.StateCode),2) AS State,
LEFT(L.PostalCode,5) AS ZipCode,
'(' + LEFT(PhoneNumber, 3) + ') ' + LEFT(RIGHT(PhoneNumber, 7), 3) + '-' + RIGHT(PhoneNumber, 4) AS PhoneNumber,
'(' + LEFT(FaxNumber, 3) + ') ' + LEFT(RIGHT(FaxNumber, 7), 3) + '-' + RIGHT(FaxNumber, 4) AS FaxNumber,
LEFT(EmailAddress, 100) AS EmailAddress,
L.Latitude,
L.Longitude,
ROUND(dbo.GetDistance(L.Latitude, @Latitude, L.Longitude, @Longitude), 1) AS Distance,
@Latitude AS ziplat,
@longitude AS ziplong
FROM Agents A
INNER JOIN (SELECT * FROM AgentAddresses
WHERE AddressTypeID = 1
AND Latitude IS NOT NULL) L
ON A.AgentId = L.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS PhoneNumber
FROM AgentContactMethods WHERE ContactMethodTypeId = 1
AND
LEN(ContactMethodValue) = 10) AS P
ON A.AgentId = P.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS FaxNumber
FROM AgentContactMethods
WHERE ContactMethodTypeId = 2
AND
LEN(ContactMethodValue) = 10) AS F
ON A.AgentId = F.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS EmailAddress
FROM AgentContactMethods
WHERE ContactMethodTypeId = 3) AS E
ON A.AgentId = E.AgentId
WHERE HomeWiseAgentId IS NOT NULL
AND
P.AgentId IS NOT NULL
AND
IsOnWebsite = 1
ORDER BY
Distance
IF EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode)
BEGIN
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'TX')
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance)
FROM #temp_Agents)
ELSE @MaxDistance
END)
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'LA')
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
IF EXISTS (SELECT State FROM #temp_Agents WHERE State NOT LIKE ('TX''LA''FL'))
SET @ErrMsg = '4 - Custom Error # 4 - All Other States. ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'FL')
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
END
ELSE IF @ZipCode LIKE 'TX'
BEGIN
SET @ErrMsg = '2 - Custom Error # 2 -Texas ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
ELSE IF @ZipCode LIKE 'LA'
BEGIN
SET @ErrMsg = '3 - Custom Error # 3 - Louisiana ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
ELSE IF @ZipCode NOT LIKE ('TX''LA''FL')
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
ELSE
BEGIN
SET @ErrMsg = '1 - Custom Error Message # 1 - Not a Valid ZIP Code ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
END
July 22, 2008 at 8:12 am
What is the error message?
If it was easy, everybody would be doing it!;)
July 22, 2008 at 8:37 am
ctics112 (7/21/2008)
IF EXISTS (SELECT State FROM #temp_Agents WHERE State NOT LIKE ('TX''LA''FL'))SET @ErrMsg = '4 - Custom Error # 4 - All Other States. ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
The IF statement above isn't blocked with a BEGIN-END. Could that be causing your problem?
July 22, 2008 at 8:41 am
I got rid of the error message by changing LIKE to IN and added BEGIN END statements to my RAISERROR messages. I am still having trouble returning zip codes outside of FL (TX,LA). If i type a TX zip code it returns a location in Florida which is about 500 miles away(weird). I will ponder this over for the day. Feel free to comment if you see something wrong. This is what the updated code looks like.
ALTER PROCEDURE [dbo].[spGetAgentsCloseToZipCode]
@NumAgents INT,
@ZipCode VARCHAR(5),
@MaxDistance NUMERIC(18,6)
AS
DECLARE @Latitude NUMERIC(18,6)
DECLARE @Longitude NUMERIC(18,6)
DECLARE @Locations INT
DECLARE @ErrMsg VARCHAR(MAX)
DECLARE @ScenarioID INT
SET @Latitude = 26.0
SET @Longitude = -80.0
Set @ZipCode = RTRIM(LTRIM(CAST(@ZipCode AS VARCHAR(5))))
-- Get ZipCode Lat/Long
SELECT
@Locations = COUNT(*)
FROM
ZipCodes
WHERE
ZipCode = @ZipCode
AND
(StateAbbr IN ('TX','LA','FL'))
IF @Locations > 0
BEGIN
SELECT
@Latitude = Latitude,
@Longitude = Longitude
FROM
ZipCodes
WHERE
ZipCode = @ZipCode
AND
(StateAbbr IN ('TX','LA','FL'))
CREATE TABLE #temp_Agents
(
TempAgentId INT IDENTITY(1,1) NOT NULL,
AgentId INT NOT NULL,
HomeWiseAgentID VARCHAR(50) NOT NULL,
AgentName VARCHAR(50) NOT NULL,
ContactName VARCHAR(50) NOT NULL,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
City VARCHAR(100),
State VARCHAR(2),
ZipCode VARCHAR(5),
PhoneNumber VARCHAR(15),
FaxNumber VARCHAR(15),
EmailAddress VARCHAR(100),
Latitude NUMERIC(18,6),
Longitude NUMERIC(18,6),
Distance NUMERIC(18,6),
ziplat NUMERIC(18,6),
ziplong NUMERIC(18,6)
)
INSERT INTO #temp_Agents
(
AgentId,
HomeWiseAgentId,
AgentName,
ContactName,
Address1,
Address2,
City,
State,
ZipCode,
PhoneNumber,
FaxNumber,
EmailAddress,
Latitude,
Longitude,
Distance,
ziplat,
ziplong
)
-- Only SELECT TOP 100 to keep speed, if result set can be >100, increase TOP
SELECT TOP 100
A.AgentId,
A.HomeWiseAgentId,
A.AgentName,
A.ContactName,
LEFT(L.Address1,100),
LEFT(L.Address2,100),
LEFT(L.City,100),
LEFT(UPPER(L.StateCode),2) AS State,
LEFT(L.PostalCode,5) AS ZipCode,
'(' + LEFT(PhoneNumber, 3) + ') ' + LEFT(RIGHT(PhoneNumber, 7), 3) + '-' + RIGHT(PhoneNumber, 4) AS PhoneNumber,
'(' + LEFT(FaxNumber, 3) + ') ' + LEFT(RIGHT(FaxNumber, 7), 3) + '-' + RIGHT(FaxNumber, 4) AS FaxNumber,
LEFT(EmailAddress, 100) AS EmailAddress,
L.Latitude,
L.Longitude,
ROUND(dbo.GetDistance(L.Latitude, @Latitude, L.Longitude, @Longitude), 1) AS Distance,
@Latitude AS ziplat,
@longitude AS ziplong
FROM Agents A
INNER JOIN (SELECT * FROM AgentAddresses
WHERE AddressTypeID = 1
AND Latitude IS NOT NULL) L
ON A.AgentId = L.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS PhoneNumber
FROM AgentContactMethods WHERE ContactMethodTypeId = 1
AND
LEN(ContactMethodValue) = 10) AS P
ON A.AgentId = P.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS FaxNumber
FROM AgentContactMethods
WHERE ContactMethodTypeId = 2
AND
LEN(ContactMethodValue) = 10) AS F
ON A.AgentId = F.AgentId
LEFT OUTER JOIN (SELECT AgentId, ContactMethodValue AS EmailAddress
FROM AgentContactMethods
WHERE ContactMethodTypeId = 3) AS E
ON A.AgentId = E.AgentId
WHERE HomeWiseAgentId IS NOT NULL
AND
P.AgentId IS NOT NULL
AND
IsOnWebsite = 1
ORDER BY
Distance
IF EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode)
BEGIN
IF EXISTS (SELECT State FROM #temp_Agents WHERE State IN ('TX'))
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance)
FROM #temp_Agents)
ELSE @MaxDistance
END)
IF EXISTS (SELECT State FROM #temp_Agents WHERE State IN ('LA'))
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
IF EXISTS (SELECT State FROM #temp_Agents WHERE State NOT IN ('TX','LA','FL'))
BEGIN
SET @ErrMsg = '4 - Custom Error # 4 - All Other States. ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
IF EXISTS (SELECT State FROM #temp_Agents WHERE State IN ('FL'))
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
END
ELSE IF @ZipCode IN ('TX')
BEGIN
SET @ErrMsg = '2 - Custom Error # 2 -Texas ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
ELSE IF @ZipCode IN ('LA')
BEGIN
SET @ErrMsg = '3 - Custom Error # 3 - Louisiana ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
ELSE IF @ZipCode NOT IN ('TX''LA''FL')
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
ELSE
BEGIN
SET @ErrMsg = '1 - Custom Error Message # 1 - Not a Valid ZIP Code ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
IF EXISTS (SELECT * FROM sys.objects WHERE name = '#temp_Agents')
DROP TABLE #temp_Agents
END
July 22, 2008 at 8:52 am
The way I am reading the procedure you are only processing zip codes for TX, FL, and LA. Is this what you want? If so there are a lot of changes that could be made to make the procedure simpler. Could you explain what you need to accomplish with the procedure? Then we may be able to offer more help.
I can give you some tips that I think will help:
1. Always use BEGIN and END with control of low statements for readability.
2. Indent and comment in the control of flow statements
3. Move your temp table creation to the top of the procedure as mixing DDL and DML usually causes recompiles.
4. This code NOT LIKE ('TX''LA''FL') returns every recod in the table including records for TX, FL, and LA because it is trying to find the exact string you have in the ()'s. I think what you want is NOT IN ('TX', 'FL', 'LA').
5. Replace LIKE with = when you are not using any wild card characters.
6. Replacing your sub-queries with joins where possible will improve readability and performance. It looks like almost all the sub-queries in the FROM clause of your temp table population could be replaced with regular joins.
This copy of the code implements suggestions 1 and 2 (from version 1 of your code):
[font="Courier New"]ALTER PROCEDURE [dbo].[spGetAgentsCloseToZipCode]
@NumAgents INT,
@ZipCode VARCHAR(5),
@MaxDistance NUMERIC(18,6)
AS
DECLARE @Latitude NUMERIC(18,6)
DECLARE @Longitude NUMERIC(18,6)
DECLARE @Locations INT
DECLARE @ErrMsg VARCHAR(MAX)
DECLARE @ScenarioID INT
SET @Latitude = 26.0
SET @Longitude = -80.0
SET @ZipCode = RTRIM(LTRIM(CAST(@ZipCode AS VARCHAR(5))))
-- Get ZipCode Lat/Long
SELECT
@Locations = COUNT(*)
FROM
ZipCodes
WHERE
ZipCode = @ZipCode AND
(StateAbbr = 'TX' OR StateAbbr = 'LA' OR StateAbbr = 'FL')
IF @Locations > 0
BEGIN
SELECT
@Latitude = Latitude,
@Longitude = Longitude
FROM
ZipCodes
WHERE
ZipCode = @ZipCode AND
(StateAbbr = 'FL' OR StateAbbr = 'TX')
CREATE TABLE #temp_Agents
(
TempAgentId INT IDENTITY(1,1) NOT NULL,
AgentId INT NOT NULL,
HomeWiseAgentID VARCHAR(50) NOT NULL,
AgentName VARCHAR(50) NOT NULL,
ContactName VARCHAR(50) NOT NULL,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
City VARCHAR(100),
State VARCHAR(2),
ZipCode VARCHAR(5),
PhoneNumber VARCHAR(15),
FaxNumber VARCHAR(15),
EmailAddress VARCHAR(100),
Latitude NUMERIC(18,6),
Longitude NUMERIC(18,6),
Distance NUMERIC(18,6),
ziplat NUMERIC(18,6),
ziplong NUMERIC(18,6)
)
INSERT INTO #temp_Agents
(
AgentId,
HomeWiseAgentId,
AgentName,
ContactName,
Address1,
Address2,
City,
State,
ZipCode,
PhoneNumber,
FaxNumber,
EmailAddress,
Latitude,
Longitude,
Distance,
ziplat,
ziplong
)
-- Only SELECT TOP 100 to keep speed, if result set can be >100, increase TOP
SELECT TOP 100
A.AgentId,
A.HomeWiseAgentId,
A.AgentName,
A.ContactName,
LEFT(L.Address1,100),
LEFT(L.Address2,100),
LEFT(L.City,100),
LEFT(UPPER(L.StateCode),2) AS State,
LEFT(L.PostalCode,5) AS ZipCode,
'(' + LEFT(PhoneNumber, 3) + ') ' + LEFT(RIGHT(PhoneNumber, 7), 3) + '-' + RIGHT(PhoneNumber, 4) AS PhoneNumber,
'(' + LEFT(FaxNumber, 3) + ') ' + LEFT(RIGHT(FaxNumber, 7), 3) + '-' + RIGHT(FaxNumber, 4) AS FaxNumber,
LEFT(EmailAddress, 100) AS EmailAddress,
L.Latitude,
L.Longitude,
ROUND(dbo.GetDistance(L.Latitude, @Latitude, L.Longitude, @Longitude), 1) AS Distance,
@Latitude AS ziplat,
@longitude AS ziplong
FROM
Agents A INNER JOIN
(SELECT * FROM AgentAddresses WHERE AddressTypeID = 1 AND Latitude IS NOT NULL) L ON
A.AgentId = L.AgentId LEFT OUTER JOIN
(SELECT AgentId, ContactMethodValue AS PhoneNumber FROM AgentContactMethods
WHERE ContactMethodTypeId = 1 AND LEN(ContactMethodValue) = 10) AS P ON
A.AgentId = P.AgentId LEFT OUTER JOIN
(SELECT AgentId, ContactMethodValue AS FaxNumber FROM AgentContactMethods
WHERE ContactMethodTypeId = 2 AND LEN(ContactMethodValue) = 10) AS F ON
A.AgentId = F.AgentId LEFT OUTER JOIN
(SELECT AgentId, ContactMethodValue AS EmailAddress FROM AgentContactMethods
WHERE ContactMethodTypeId = 3) AS E ON
A.AgentId = E.AgentId
WHERE
HomeWiseAgentId IS NOT NULL AND
P.AgentId IS NOT NULL AND
IsOnWebsite = 1
ORDER BY
Distance
IF EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode)
BEGIN
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'TX')
BEGIN
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance)
FROM #temp_Agents)
ELSE @MaxDistance
END)
END -- TEXAS
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'LA')
BEGIN
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
END --LOUISIANA
IF EXISTS (SELECT State FROM #temp_Agents WHERE State NOT LIKE ('TX''LA''FL'))
BEGIN
SET @ErrMsg = '4 - Custom Error # 4 - All Other States. ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END -- NOT TEXAS, LOUSISANA, FLORIDS
IF EXISTS (SELECT State FROM #temp_Agents WHERE State LIKE 'FL')
BEGIN
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
END -- FLORIDA
END -- IF EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode)
ELSE -- EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode) = FALSE
BEGIN
IF @ZipCode LIKE 'TX'
BEGIN
SET @ErrMsg = '2 - Custom Error # 2 -Texas ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END -- @ZipCode LIKE 'TX'
ELSE -- @ZipCode NOT LIKE 'TX'
IF @ZipCode LIKE 'LA'
BEGIN
SET @ErrMsg = '3 - Custom Error # 3 - Louisiana ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END -- @ZipCode NOT LIKE 'TX'
ELSE -- @ZipCode NOT LIKE 'TX' OR 'LA'
IF @ZipCode NOT LIKE ('TX''LA''FL')
BEGIN
SELECT * FROM #temp_Agents
WHERE TempAgentId <= @NumAgents
AND
Distance <= (CASE WHEN
(SELECT COUNT(*) FROM #temp_Agents
WHERE Distance <= @MaxDistance) = 0
THEN
(SELECT MIN(Distance) FROM #temp_Agents)
ELSE @MaxDistance
END)
END -- @ZipCode NOT LIKE 'TX' OR 'LA'
ELSE -- @ZipCode NOT LKE 'TX' OR 'LA' OR 'FL'
BEGIN
SET @ErrMsg = '1 - Custom Error Message # 1 - Not a Valid ZIP Code ' + CAST(@ScenarioId AS VARCHAR) + '.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END
END -- EXISTS (SELECT ZipCode FROM #temp_Agents WHERE ZipCode = @ZipCode) = FALSE
END -- end @locations > 0[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 8:56 am
Not sure if this is the source of the problem you are experiencing...
Doesn't this list need to be comma-delimited? It is down near the bottom of your code.
ELSE IF @ZipCode NOT IN ('TX''LA''FL')
If it was easy, everybody would be doing it!;)
July 22, 2008 at 9:26 am
Jack,
My apologies for not explaining the situation thoroughly. The stored proc is returning agents from states other than TX, FL, and LA. It is just that those states are primarily where the core of the agents are located. There are some in NY,KS...etc. The agents are returned in a distance order in respect to the zip code that is inputted. The stored proc first checks to see if the inputted zip code is in the temp_table, if it is then the process continues if not then it returns the error message at the very bottom. Next it checks to see what state the zipcode is in. Depending on what state the zip code is in will determine which IF statement gets executed. Example say i want to find the closest 25 agents(@NumAgents=25) in a 10 mile radius(@MaxDistance = 10) in the zip code = 33315 (@ZipCode =33315). It will pull the closest 25 agents to that zip code. I applied 3, 4, and 5 from the tips. I am not sure if 6 is possible but i will try. Thanks for the tips.
-Chris
July 22, 2008 at 9:31 am
Trader Sam,
Yes this list needs to be comma-delimited. I applied the changes however this does not solve my problem. thanks for pointing that out though. I still in my early stages of being a jr. dba
July 22, 2008 at 10:33 am
ctics112 (7/22/2008)
Jack,My apologies for not explaining the situation thoroughly. The stored proc is returning agents from states other than TX, FL, and LA. It is just that those states are primarily where the core of the agents are located. There are some in NY,KS...etc. The agents are returned in a distance order in respect to the zip code that is inputted. The stored proc first checks to see if the inputted zip code is in the temp_table, if it is then the process continues if not then it returns the error message at the very bottom. Next it checks to see what state the zipcode is in. Depending on what state the zip code is in will determine which IF statement gets executed. Example say i want to find the closest 25 agents(@NumAgents=25) in a 10 mile radius(@MaxDistance = 10) in the zip code = 33315 (@ZipCode =33315). It will pull the closest 25 agents to that zip code. I applied 3, 4, and 5 from the tips. I am not sure if 6 is possible but i will try. Thanks for the tips.
-Chris
You are welcome.
So the main requirement is to find n closest agents with n miles of the zip?
I think this code will do that. It does not have all the scenarios you are coding around, but this should meet the main requirements:
[font="Courier New"]ALTER PROCEDURE [dbo].[spGetAgentsCloseToZipCode]
@NumAgents INT,
@ZipCode VARCHAR(5),
@MaxDistance NUMERIC(18,6)
AS
DECLARE @Latitude NUMERIC(18,6)
DECLARE @Longitude NUMERIC(18,6)
DECLARE @ErrMsg VARCHAR(MAX)
-- get lat and long for zip
SELECT
@Latitude = Latitude,
@Longitude = Longitude
FROM
ZipCodes Z
WHERE
ZipCode = @ZipCode
-- have a vailid zip with a lat and long
IF @Latitude IS NOT NULL
BEGIN
-- get the top N agents within N miles
SELECT TOP @NumAgents
A.AgentId,
A.HomeWiseAgentId,
A.AgentName,
A.ContactName,
LEFT(AA.Address1,100),
LEFT(AA.Address2,100),
LEFT(AA.City,100),
LEFT(UPPER(AA.StateCode),2) AS State,
LEFT(AA.PostalCode,5) AS ZipCode,
'(' + LEFT(AA.PhoneNumber, 3) + ') ' + LEFT(RIGHT(AA.PhoneNumber, 7), 3) + '-' + RIGHT(AA.PhoneNumber, 4) AS PhoneNumber,
'(' + LEFT(AA.FaxNumber, 3) + ') ' + LEFT(RIGHT(AA.FaxNumber, 7), 3) + '-' + RIGHT(AA.FaxNumber, 4) AS FaxNumber,
LEFT(AA.EmailAddress, 100) AS EmailAddress,
ROUND(dbo.GetDistance(L.Latitude, @Latitude, L.Longitude, @Longitude), 1) AS Distance
FROM
Agents A JOIN
AgentAddress AA ON
A.AgentID = AA.AgentID AND
AA.AddressTypeID = 1 AND
AA.Latitude IS NOT NULL LEFT JOIN
AgentContactMethod ACM1 ON
A.AgentId = ACM1.AgentId AND
ACM1.ContactMethodTypeId = 1 AND
LEN(ACM1.ContactMethodValue) = 10) LEFT JOIN
AgentContactMethod ACM2 ON
A.AgentId = ACM2.AgentId AND
ACM2.ContactMethodTypeId = 2 AND
LEN(ACM2.ContactMethodValue) = 10) LEFT JOIN
AgentContactMethod ACM3 ON
A.AgentId = ACM3.AgentId AND
ACM3.ContactMethodTypeId = 3 AND
LEN(ACM3.ContactMethodValue) = 10)
WHERE
ROUND(dbo.GetDistance(AA.Latitude, @Latitude, AA.Longitude, @Longitude) <= @MaxDistance
ORDER BY
Distance
END
ELSE -- no lat or long found so not a valid zip
BEGIN
SET @ErrMsg = '1 - Custom Error Message # 1 - ' + @ZipCode + ' Is Not a Valid ZIP Code.'
RAISERROR(@ErrMsg, 16, 1)
RETURN 1
END[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 11:24 am
Jack,
Yes that is the main requirement for this stored proc. The code that you provided looks like it will fulfill that requirement. This looks much cleaner than the code that I previously had. I will apply the remaining scenarios and let you know how that goes probably sometime tomorrow. Again thanks for the help and guidance.
-Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply