Single Query To Select Based On Parameters If Or not supplied-CodePudding

I have used SqlDataSource and have a select query based on District & Zone as below

SELECT a.[committee_id] memberid, a.[membername], a.[memberemail], a.[memberdesignation], a.[membercreatedby],b.districtname AS district,b.districtid,c.zone_name AS zone,c.zoneid
FROM [committee_details] a
LEFT JOIN district_master b on b.districtid=a.districtid
LEFT JOIN zone_master c on c.districtid=a.districtid and c.zoneid = a.zoneid
WHERE (a.[membercreatedby] = 'director') AND ((convert(varchar,a.districtid) LIKE '%2%') AND (convert(varchar,a.zoneid) LIKE '%%')) ORDER BY a.[committee_id] DESC

It's an inline query. I have tried above query but not able to figure out how to Select condition based.

I want if district supplied then Select according to District, if both District & Zone supplied then Select according to both & If nothing supplied then Select All. But should be in single query. How should I do this?

CodePudding user response：

First, fix your query so you are not using meaningless table aliases. Use table abbreviations! I would also drop all the square braces; they just make the query harder to write and to read.

Basically, you want comparisons with NULL in the WHERE clause. I have no idea why your sample code uses LIKE, particularly columns that appear to be numbers. Nothing in the question explains why LIKE is used for the comparison, so the idea is:

SELECT cd.committee_id as memberid, cd.membername, 
       cd.memberemail, cd.memberdesignation, cd.membercreatedby,
       dm.districtname AS district, dm.districtid,
       zm.zone_name AS zone, zm.zoneid
FROM committee_details cd LEFT JOIN
     district_master dm 
     ON cd.districtid = dm.districtid LEFT JOIN
     zone_master zm
     ON zm.districtid = cd.districtid AND
        zm.zoneid = cd.zoneid
WHERE cd.membercreatedby = 'director') AND 
      (cd.districtid = @district or @district is null) AND
      (cd.zoneid = @zone or @zone is null) 
ORDER BY cd.[committee_id] DESC;

If you were using LIKE, then I would phrase the logic like:

WHERE cd.membercreatedby = 'director') AND 
      (cast(cd.districtid as varchar(255)) like @district) AND
      (cast(cd.zoneid as varchar(255)) like @zone)

And pass in the patterns as '%' when you want all values to match. This assumes that the columns in cd are not NULL. If they can be NULL, then you want an explicit comparison, as in the first example.

CodePudding user response：

If I got the question right then you can use parameters and compare to the column itself if the values are not supplied or not present.

try the following:

SELECT a.[committee_id] memberid, a.[membername], a.[memberemail], a.[memberdesignation], a.[membercreatedby],b.districtname AS district,b.districtid,c.zone_name AS zone,c.zoneid
FROM [committee_details] a
LEFT JOIN district_master b on b.districtid=a.districtid
LEFT JOIN zone_master c on c.districtid=a.districtid and c.zoneid = a.zoneid
WHERE (a.[membercreatedby] = 'director') 
AND b.districtname = isnull(nullif(@districtname, ''), b.districtname)
AND c.zone_name = isnull(nullif(@zone_name, ''), c.zone_name)
ORDER BY a.[committee_id] DESC