SQL Server 2008 R2 issue with like clause

Question

I want to perform search on any column of the table which user will select.For this I have two string variable searchkey to store name of column and searchvalue for value to be searched.

This is my query for the same:

and (Select Case @SearchKey 
            When 'Title' then Parcel.Title 
            When 'Unit_No' then Unit_No 
            When 'AgentName' then App_User.Name
            When 'TenantName' then Client_Personal_Information.First_Name
            When 'UnitRefNo' then Unit_Ref_No

                        End )  like @SearchValue

this query is working fine and giving me desired output as all column have nvarchar type values.

But when I added this in above query

When 'Rent' then Unit_Transform.Rent_Per_Annum
When 'SecurityDeposit' then [Unit_Transform].[Security_Deposit] 

for columns which holds decimal values it only returns output for those two column. If I select column which hold nvarchar values it shows no record found.

Any ideas why is this happening.

Answer 1

I think you need to CAST your decimal fields to varchar like this:

When 'Rent' then CAST( Unit_Transform.Rent_Per_Annum as Varchar )

And here is the Fiddle to see. Remove the cast and you actually receive an error.

Good luck.