Linked Questions

Popular Questions

Where clause with multiple columns as parameter

Asked by At

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.

  1. 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.

Code Sample

    (CASE j1.Field3 when '' THEN '-' Else j1.Field3 END) as [SerialNo],
    JobOrder j1
...Lots of joins
    (@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.

Related Questions