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
