April 26, 2012 at 4:20 am
DESCRIPTION OF PROBLEM
--------------------------------------------------
A template ASP landing page uses the following SQL to create a list of properties, 10 at a time, for each landing page. Landing pages are dedicated to a different type of property and location.
We wish to force certain featured properties to be on Page 1 above the remainder of the results (which are sorted by price), but need different properties to be forced depending on the page.
At present we have a field called LANDINGFEATURE which is set as 'Yes', upon which the results are sorted, but these apply erroneously across all landing pages. So a house in New York may be forced correctly onto Page 1 of the New York landing page, but it will also be forced onto the USA property landing page.
Therefore, is it possible to pass in an identifier to the procedure and to sort properties depending on that identifier (e.g. New York Property) above the rest of the results?
Hope you can help. Many thanks.
EDIT: I couldn't get the highlighting with a colour to work.
SQL PROCEDURE
----------------------------------
CREATE PROCEDURE LandingPage
@PageNumber int,
@Country VARCHAR(100),
@Region VARCHAR(100),
@County VARCHAR(50),
@Location VARCHAR(100),
@PropType VARCHAR(50),
@SortColumn VARCHAR(20)
AS
DECLARE @sql AS NVARCHAR(max)
BEGIN
SET @PageNumber=(@PageNumber-1)*10
SELECT @sql = 'SELECT TOP(10) * FROM ('
SELECT @sql = @sql + 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY LandingFeature DESC,'
IF @SortColumn = 'DESC'
SELECT @sql = @sql + 'Euros DESC),'
IF @SortColumn = 'ASC'
SELECT @sql = @sql + 'Euros ASC),'
IF @SortColumn = 'POPULAR'
SELECT @sql = @sql + 'viewed DESC),'
IF @SortColumn = 'RECENT'
SELECT @sql = @sql + 'Date DESC),'
SELECT @sql = @sql + 'Count(*) OVER() As TotalRecords,Date,ID,Country,Region,County,Location,Type,Bedrooms,Price,Currency,Pic'
SELECT @sql = @sql + ' FROM Properties WHERE DeleteMarker <> ''Yes'' AND Hide <> ''Yes'''
IF @Country is not null
SELECT @sql = @sql + ' AND Country=''' + @Country + ''''
IF @Region is not null
SELECT @sql = @sql + ' AND Region=''' + Replace(@Region,'#','''''') + ''''
IF @County is not null
SELECT @sql = @sql + ' AND County=''' + Replace(@County,'#','''''') + ''''
IF @Location is not null
SELECT @sql = @sql + ' AND Location LIKE ''%' + Replace(@Location,'#','''''') + '%'''
IF @PropType is not null
SELECT @sql = @sql + ' AND Type=''' + @PropType + ''''
SELECT @sql = @sql + 'WHERE TAB.RowId > ' + CAST(@PageNumber AS VARCHAR)
EXECUTE SP_EXECUTESQL @sql
END
April 26, 2012 at 5:29 am
Which input parameter you want to use to define the order? Which column should it apply to?
You can do something like:
ORDER BY (CASE WHEN SomeColumn = @HigherPriorityValue THEN 1 ELSE 2 END), SomeColumn
April 26, 2012 at 8:05 am
You have two major problems with this type of design.
1) This appears to wide open to sql injection. NEVER NEVER NEVER execute anything that the user has the ability to change. From what you posted I suspect most of these values are coming from you query string. Then you turn around and directly execute a concatenated string based on those values. SCARY!!!!!!!!!!
2) Take a look at Gail's blog for a better approach to designing this type of query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply