How To Pass Dynamic Parameter To Order BY Clause

Hi,





I use a DataGrid to show the data, and I want it has a sorting and Paging function,


so I use dataset to collect the data from middle tier function and stored procedure.


I have code in aspx page like BindData(ViewState("SortExpr")).





In the stored procedure I pass SortExpr as parameter as following:





CREATE Procedure Ruying_AutoSearch10


(


@Make varchar(50),


@Model varchar(50) = NULL,


@Condition varchar(20) = NULL,


@Miles float,


@Zipcode varchar(5),


@SortExpr varchar(100)


)


AS





DECLARE @RowCount int


SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'





if @RowCount > 0


BEGIN


SELECT


z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance


/*


The above functions requires the Distance Assistant.


*/


FROM


ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a


WHERE


z.Latitude <= r.MaxLat


AND z.Latitude >= r.MinLat


AND z.Longitude <= r.MaxLong


AND z.Longitude >= r.MinLong


AND z.CityType = 'D'


AND z.ZIPCodeType <> 'M'


AND z.ZIPCode = a.Zipcode


AND a.AdActive = '1'


AND a.AdExpiredate >= getdate()


AND a.Make = @Make


AND a.Model = IsNull(@Model,a.Model)


AND a.Condition = IsNull(@Condition, a.Condition)


AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles


ORDER BY @SortExpr


END


ELSE


SELECT -1 As ZIPCode


--ZIP Code not found...


GO








but I got the error as "variables are only allowed when ordering by an expression referenceing


a column name". How I fix this error? Please help.





Thanks.





Lin