There are only two parameters passed to the SP. The problem is slightly complicated to explain fully but essentially what's happening is that the second and third IF statements do not return any values and I've no idea why, as the SELECT statement is almost identical in all four.
ALTER PROCEDURE developers.fort_GetSearchResults
(
@.propertyType int,
@.county int
)
AS
IF ((@.propertyType = 0) AND (@.county = 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
ORDER BY NEWID()
ELSE
IF (@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
ELSE
IF (@.county = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode,
Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax,
Experimental_ContactDetails.email, Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
ELSE
IF ((@.propertyType != 0) AND (@.county != 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description,
Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading,
Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
AND (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
RETURN
Condition 2 and 3 may have some no inclusives here:
1. ((@.propertyType = 0) AND (@.county = 0))
2. ((@.propertyType = 0) AND (@.county != 0))
3. ((@.propertyType != 0) AND (@.county = 0))
4. ((@.propertyType != 0) AND (@.county != 0))
Could you try these conditions and check SQL statement with hard coded value whether they are returning results/
From your script, my guess is:
1. ((@.propertyType = 0) AND (@.county = 0))
you don't use your parameters' value in your sql
2. ((@.propertyType = 0) AND (@.county != 0))
you may need (@.county value in your sql instead of @.propertyType
3. ((@.propertyType != 0) AND (@.county = 0))
you may need (@.propretyType value in your sql instead of @.county
4. ((@.propertyType != 0) AND (@.county != 0))
you use both parameters' values here.
Let us know if you get this work.
Limno
|||
IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
Do you have any propertyTypes that are 0?
Motley wrote:
IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)Do you have any propertyTypes that are 0?
Yes. The first item in each dropdown list have values of '0' - the dropdown lists are databound but the first items are then inserted programmatically and given the value '0'.
In any case, if there wasn't a propertyType of 0, the IF ((@.propertyType = 0) AND (@.county = 0)) line wouldn't work either, and it does. It's strange that the only SELECT statements that don't work are the ones with only a single parameter checked by the IF statement.
No comments:
Post a Comment