I am using jQuery datatables to display data to the user. And like to use column filtering. I am not able to modify existing query for multiple column filter requests. And experiencing problems with making it dynamic since it's too complex.
I want to create a dynamic where clause. Assume user queries two column, lets say CustomerName and Location. I want them to be appended to the query which already has some where conditions exists.
What I have tried
I did tried some amazing crazy things yet failed.
I've created a table from parameters by splitting them ( CustomerName:Test,Location:Germany) and tried such code like
@CustomSearchParam IN (SELECT temp1.Key FROM @temp WHERE temp1.Key = 'CustomerName') AND CONTAINS(Customers.CustomerName, SELECT temp1.Value FROM @temp WHERE temp1.Key = 'CustomerName'))
This attempt utterly failed, since contains expect only parameter or string.
2, Tried to make query dynamic and use
sp_executequery but again couldn't make it work.
select j1.Field1, j1.Field2, (CASE j1.Field3 when '' THEN '-' Else j1.Field3 END) as [SerialNo], from JobOrder j1 ...Lots of joins where (@pastTenDate is null or j1.InsertDate < @pastTenDate) and (@state is null or j1.State = @state) and (@statusGroup = ''-1'' OR j1.Status IN (select * from dbo.splitstringAsIntegerTable(@statusGroup))) and j1.Status <> 99 and (j1.RelatedCompanyID in (select ID from dbo.splitstringAsIntegerTable(@companyGroup)) or j1.JobOrderID in (Select JobOrderID from SharedOrders where SharedOrders.CompanyID in (select ID from dbo.splitstringAsIntegerTable(@companyGroup)))) AND (@CategoryID is null or [email protected]) AND OR @CustomSearchParam='DeviceBrand' AND CONTAINS(DeviceBrand.BrandName,@SearchWithWildcard) OR @CustomSearchParam='DeviceType' AND CONTAINS(DeviceType.DeviceTypeName,@SearchWithWildcard) OR @CustomSearchParam='DeviceModel' AND CONTAINS(DeviceModel.DeviceName,@SearchWithWildcard) ...couple thousands conditions more
Here I want to add a dynamic query according to queried columns
Actually I don't want to make query dynamic, but yet it seems the only way. I am looking for a solution without dynamic query stuff.
Just want to add where conditions according to the column names and it's related search parameters.