Query problem

[firestoper]

Hi Guru's, I made a basic search filtering query, it searches for the predefine (default) values when user does not place any values on search fields, my search query works fine but not until having the sort option, below is my query and my db structure: TABLE: ListingID int HLCN varchar(6) HousePrice money Bathrooms float Bedrooms int HomeLocation varchar(50) ContactName varchar(50) EmailContact varchar(50) PhoneContact varchar(50) Address varchar(100) SquareFeet varchar(50) YearBuilt varchar(50) LotSize varchar(50) Garage varchar(50) Notes varchar(255) QUERY: declare @sort nvarchar(50) set @sort ='HomeLocation' Select listingID, HLCN, HousePrice, Bedrooms, Bathrooms, HomeLocation, ContactName from listings where houseprice between 0 and 100000000 and bathrooms >= 0 and bedrooms >= 0 and homelocation like '%' + '' + '%' order by case @sort when 'HomeLocation' then HomeLocation when 'HousePrice' then HousePrice end when I sort by HousePrice it return correct resultm but when I sort by Homelocation it returned Cannot convert a char value to money. The char value has incorrect syntax. I'm wondering whey It's causing char-to- money conversion error when I'm not passing char values and also this only happens when I'm using HomeLocation as sort. Please advice, Thanks Dom

[Eliz k]

I believe it is because the HomeLocation is VARCHAR type while the HomePrice is MONEY type. I try the following and it works (LoginID is VARCHAR, UserID is INT): DECLARE @sort NVARCHAR(50) SET @sort ='LoginID' SELECT * FROM tblUser ORDER BY CASE @sort WHEN 'LoginID' THEN CONVERT(VARCHAR, LoginID) WHEN 'UserID' THEN CONVERT(VARCHAR, UserID) END Eliz.K

www.oin1.com

[firestoper]

Works great Eliz Thank you Dom

[firestoper]

Works great Eliz Thank you Dom