Click here to Skip to main content
14,271,310 members
Rate this:
Please Sign up or sign in to vote.
Hello Coders,
I need your help in writing one sql query, i know we can not use directly IF condition in where clause so tell me its alternative.
My scenario is i am doing filter work by using 3 dropdowns and 1 checkbox. Till 3 dropdowns i am able to filter data correctly, now by using checkbox i have to filter my result.
I have product list and i want if user check the checkbox then my result show those records which have atleast one image. Every product have 5 fields for images and i want if any field have value then it should be show otherwise it doesn't include in my result.

<asp:CheckBox ID="chk_image" runat="server" oncheckedchanged="chk_image_CheckedChanged" /> Exclude Without Image Ads
protected void chk_image_CheckedChanged(object sender, EventArgs e)
    {
        Refine_BindRepeaterData();
    }

protected int Refine_BindRepeaterData()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("sps_searchresult", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@country", 1);
        cmd.Parameters.AddWithValue("@state", dd_state.SelectedValue);
        cmd.Parameters.AddWithValue("@pincode", txt_pincode.Text);
        cmd.Parameters.AddWithValue("@category", dd_category.SelectedValue);
        cmd.Parameters.AddWithValue("@subcategory", dd_subcategory.SelectedValue);
        cmd.Parameters.AddWithValue("@OrderBy", dd_sort.SelectedValue);
        //cmd.Parameters.AddWithValue("@iam", dd_postedby.SelectedValue);
        string iam = dd_postedby.SelectedValue;
        if (iam == "All")
        {
            cmd.Parameters.AddWithValue("@iam", DBNull.Value);
        }
        else
        {
            cmd.Parameters.AddWithValue("@iam", dd_postedby.SelectedValue);
        }
        cmd.Parameters.AddWithValue("@image", chk_image.Checked);
        con.Close();
}

ALTER PROCEDURE [dbo].[sps_searchresult]
	@country int,
	@state nvarchar(20),
	@pincode nvarchar(10),
	@category int,
	@subcategory nvarchar(25),
	@OrderBy nvarchar(50),
	@iam nvarchar(50),
	@chkimage bit
	
AS
BEGIN
	
	if(@state='--Select State--' and @pincode='Pincode' and @subcategory='--Select Sub-Category--')
	begin
	select r.*, s.s_name, c.category_name, sc.subcategory_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,250) AS shortdescription, substring(title,1,30) AS shorttitle, substring(address,1,80) AS shortaddress from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id join tbl_category as c on r.category=c.category_id INNER JOIN tbl_adsubcategory sc on r.ad_id=sc.ad_id 
	where country=@country and category=@category and iam = ISNULL(@iam, iam) and 
	@chkimage=false
		(imagepath1!='' or imagepath2!='' or imagepath3!='' or imagepath4!='' or imagepath5!='')
	
	
	end
end

in where i have to check @chkimage value if it is true then filter result by excluding ad without image and if it is false then result must not be affected(means it should not consider this parameter, just like previous one do : iam = ISNULL(@iam, iam))
Posted
Updated 27-Nov-14 10:53am
v2
Comments
PIEBALDconsult 27-Nov-14 16:30pm
   
You can use a CASE in a WHERE clause.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Don't think of it as an IF-statement:
... AND (@chkimage=0 or imagepath1!='' or imagepath2!='' or imagepath3!='' or imagepath4!='' or imagepath5!='')

The above says one of the conditions has to be true. So either @chkimage is off or one of the image paths is not empty i.e. @chkimage=0 will assure that the above condition is true regardless of the values in image path 1-5.

Please note that empty string is not equivalent to NULL in MS SQL Server.
   
v2
Comments
Raj Negi 28-Nov-14 3:17am
   
Perfect. Thanks a lot. :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

first of all I think parameter name is not
@image
it is
@chkImage


simply put if and else part for it as under:
if(@chkImage = true)
select r.*, s.s_name, c.category_name, sc.subcategory_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,250) AS shortdescription, substring(title,1,30) AS shorttitle, substring(address,1,80) AS shortaddress from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id join tbl_category as c on r.category=c.category_id INNER JOIN tbl_adsubcategory sc on r.ad_id=sc.ad_id 
	where country=@country and category=@category and iam = ISNULL(@iam, iam) and (imagepath1!='' or imagepath2!='' or imagepath3!='' or imagepath4!='' or imagepath5!='')

else -- exclude image check
select r.*, s.s_name, c.category_name, sc.subcategory_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,250) AS shortdescription, substring(title,1,30) AS shorttitle, substring(address,1,80) AS shortaddress from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id join tbl_category as c on r.category=c.category_id INNER JOIN tbl_adsubcategory sc on r.ad_id=sc.ad_id 
	where country=@country and category=@category and iam = ISNULL(@iam, iam) 
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

You can use CASE instead of IF statement

Example:
CASE WHEN value1 IS NOT NULL THEN 'value1' ELSE 'value2' END VALUE
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

select r.*,
s.s_name,
c.category_name,
sc.subcategory_name,
CASE iam WHEN 1
THEN Professional/Business
ELSE Individual End as iamdetail,
substring(description,1,250) AS shortdescription,
substring(title,1,30) AS shorttitle,
substring(address,1,80) AS shortaddress from tbl_adregister r
INNER JOIN tbl_state s ON r.state = s.s_id
join tbl_category as c on r.category = c.category_id
INNER JOIN tbl_adsubcategory sc on r.ad_id = sc.ad_id
where country = @country and category = @category and iam = ISNULL(@iam, iam)
Or
(@chkimage = 0 and (imagepath1 != '' or imagepath2 !='' or imagepath3 != '' or imagepath4 != '' or imagepath5 != ''))

Here, if the @chkimage is 0(false) then it will check the condition otherwise not.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100