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