Is this possible? Stored procedure sorting...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is this possible? Stored procedure sorting...

Author

Topic

V8S
Starting Member

3 Posts

Posted - 2012-04-26 : 06:12:09

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 (red text below), 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.

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 + ') TAB '
SELECT @SQL = @SQL + 'WHERE TAB.RowId > ' + CAST(@PageNumber AS VARCHAR)

EXECUTE SP_EXECUTESQL @SQL
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:36:26

its possible so far identifier has a valid column within table
in that case statement will become


..
SELECT @SQL = @SQL + 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY '+ @NewIdentifier + ' DESC,'
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources