Hi all,
I have a SQL statement that allows paging and dynamic sorting of the
columns, but what I can't figure out without making the SQL a dynamic
string and executing it, or duplicating the SQL statement between an
IF and ELSE statement.
Following is the SQL statement;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_search]
@.search VARCHAR( 80 )
, @.startRow INT = 1
, @.endRow INT = NULL
, @.postcode AS CHAR( 4 ) = NULL
, @.suburb AS VARCHAR( 40 ) = NULL
, @.stateIdentity AS TINYINT = NULL
, @.fromLatitude AS REAL = NULL -- latitude the user is located in
, @.fromLongitude AS REAL = NULL -- longitude the user is located in
, @.sort TINYINT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.calculateDistance BIT;
SET @.calculateDistance = 0;
-- get the longitude and latitude if required
IF ( NOT @.postcode IS NULL )
BEGIN
SELECTDISTINCT
@.fromLatitude = latitude
, @.fromLongitude = longitude
FROMtbl_postalcode
WHERE(postalcode = @.postcode)
SET @.calculateDistance = 1
END
ELSE IF ( NOT @.suburb IS NULL AND NOT @.stateIdentity IS NULL )
BEGIN
SELECTDISTINCT
@.fromLatitude = latitude
, @.fromLongitude = longitude
FROMtbl_locality
WHERE(locality = @.suburb)
AND(stateIdentity = @.stateIdentity)
SET @.calculateDistance = 1
END
/*
ELSE IF ( @.fromLatitude IS NULL AND @.fromLongitude IS NULL )
BEGIN
RAISERROR( 'You need to pass a valid combination to this stored
procedure, example: postcode or suburb and state identity or longitude
and latitude', 18, 1 );
END*/
SELECT D1.[row]
, D1.[totalRecordCount]
, D1.[classifiedIdentity]
, D1.[title]
, D1.[summary]
, D1.[price]
, D1.[locality]
, D1.[state]
, D1.[postcode]
, D1.[addedLast24]
, D1.[dateStamp]
, D1.[t2Rank]
, D1.[t3Rank]
, D1.[tRank]
, D1.[distance]
, F.[originalName]
, F.[extension]
, F.[uniqueName]
FROM(
-- derived table
SELECT ROW_NUMBER() OVER ( ORDER BY CASE @.sort WHEN 0 THEN
CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].
[fn_calculateDistance] ( @.fromLatitude, @.fromLongitude, L.latitude,
L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +
C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row
, COUNT( * ) OVER() AS totalRecordCount
, C.[classifiedIdentity]
, C.[title]
, C.[summary]
, C.[price]
, C.[locality]
, C.[state]
, C.[postcode]
, CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )
THEN 1 ELSE 0 END AS addedLast24
, C.[dateStamp]
/* , t1.RANK AS t1Rank */
, t2.RANK AS t2Rank
, t3.RANK AS t3Rank
, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) AS tRank
, CASE @.calculateDistance WHEN 1 THEN CEILING( [dbo].
[fn_calculateDistance] ( @.fromLatitude, @.fromLongitude, L.latitude,
L.longitude ) ) ELSE 0 END AS distance
FROM [tbl_classified] AS C
INNER JOINtbl_locality L
ONC.localityIdentity = L.localityIdentity
/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @.keyword ) AS
t1
ON FT_TBL.categoryIdentity = t1.[KEY] */
LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @.search ) AS
t2
ON C.classifiedIdentity = t2.[KEY]
LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,
@.search ) AS t3
ON C.classifiedIdentity = t3.[KEY]
WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) ) != 0
) AS D1
LEFT OUTER JOINtbl_classified_file CF
OND1.classifiedIdentity = CF.classifiedIdentity
LEFT OUTER JOINtbl_file F
ONF.fileIdentity = CF.fileIdentity
WHERE( row >= @.startRow )
AND( @.endRow IS NULL OR row <= @.endRow )
END
The part I'm having trouble with is making the sort order in the
following line dynamic
ORDER BY CASE @.sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THEN
CAST( CEILING( [dbo].[fn_calculateDistance] ( @.fromLatitude,
@.fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN
( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price AS
CHAR( 10 ) ) END ASC
any help would be greatly apprecaited.
ThanksGot this one sorted thanks...
