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
SELECT DISTINCT
@fromLatitude = latitude
, @fromLongitude = longitude
FROM tbl_postalcode
WHERE (postalcode = @postcode)
SET @calculateDistance = 1
END
ELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )
BEGIN
SELECT DISTINCT
@fromLatitude = latitude
, @fromLongitude = longitude
FROM tbl_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 JOIN tbl_locality L
ON C.localityIdentity = L.localityIdentity
/* LEFT OUTER JOIN CONTAINSTABLE( tbl_category, title, @keyword ) AS
t1
ON FT_TBL.categoryIdentity = t1.[KEY] */
LEFT OUTER JOIN CONTAINSTABLE( tbl_classified, title, @search ) AS
t2
ON C.classifiedIdentity = t2.[KEY]
LEFT OUTER JOIN CONTAINSTABLE( 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 JOIN tbl_classified_file CF
ON D1.classifiedIdentity = CF.classifiedIdentity
LEFT OUTER JOIN tbl_file F
ON F.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.
Thanks
