CODEWITHSUNDEEP
sql-server-2008-r2
DoctorAV
DoctorAV

Reputation: 1189

SQL Server 2008 R2 issue with like clause

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.

Upvotes: 3

Views: 432

Answers (1)

sgeddes
sgeddes

Reputation: 62841

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.

Upvotes: 1

Related Questions