Dynamic sort order in SQL stored proc

[GaryWoodfine]

Hey Guys I know this question has probably been asked a thousand times on this forumm, but I had a look and been searching on the net for an answer, and no one answer suits my situation. I have a Stored proc that handles paging on the server side everything works fine, until I get to the dynamic sorting. [code]SET NOCOUNT ON; Declare @StartIndex int; Set @PropCount=(SELECT COUNT(property_id ) FROM [dbo].[vwResidentialProperty] WHERE (@Area is null or parentid = @Area) and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0) and askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000) and bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10) and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0) and floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000)); /*Check to see if the Number of Properties returned is less than actual page size if it is then set the start Index to the Property Count */ if @PropCount < @Numrow begin set @StartIndex = 1; end; else begin set @StartIndex = (@PageIndex * @Numrow) + 1; end; with Property As ( SELECT top 100 percent case @SortOrder when 0 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice Asc) when 1 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice desc) when 2 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].dateadded desc) end as row, [dbo].[vwResidentialProperty].property_id , [dbo].[vwResidentialProperty].Address, [dbo].[vwResidentialProperty].askprice , [dbo].[vwResidentialProperty].dateadded, [dbo].[vwResidentialProperty].Saleterm ,[dbo].[vwResidentialProperty].locid, [dbo].[property].adbrief FROM [dbo].[vwResidentialProperty] inner join [dbo].[property] on [dbo].[property].property_id = [dbo].[vwResidentialProperty].property_id WHERE (@Area is null or parentid = @Area) and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0) and [dbo].[vwResidentialProperty].askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000) and [dbo].[vwResidentialProperty].bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10) and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0) and [dbo].[vwResidentialProperty].floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000) )

[Harini N K]

Hi The following example shows how to sort using case .. when clause:

Use Northwind
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Spe_Invoices_Sort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Spe_Invoices_Sort]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].Spe_Invoices_Sort
@SortColumn nvarchar(25),
@SortDirn nvarchar(25)
AS
BEGIN

SELECT * FROM Invoices
ORDER BY
CASE WHEN @SortColumn = 'ShipCity' AND @SortDirn = 'DESC' THEN ShipCity END DESC,
CASE WHEN @SortColumn = 'ShipCity' AND @SortDirn = 'ASC' THEN ShipCity END ASC

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Excute this SP in Northwind database. Try this

exec Spe_Invoices_Sort 'ShipCity','asc

Replace ASC or DESC as 1 or 2 as your wish Hope this helps your problem :)

Harini