使用存储过程时出现问题，请帮忙 [英] Problem using stored procedure please help

问题描述

我已经存储了过滤程序.

I have stored procedure for filtration.

ALTER PROCEDURE [dbo].[SearchEmployee](@countryid int=null, @cityid int=null,@branchid int=null,@deptid int=null)


AS
BEGIN
    select distinct e.EMPLOYEE_ID, e.DEPARTMENT_ID,e.FIRST_ENAME, e.LAST_ENAME, e.MOBILE_NO, e.LANDLINE, e.EMAIL_ID, e.ADDRESS from EMPLOYEE_INFO e, BRANCH_MASETR b, DEPARTMENT_MASTER d, EMPLOYEE_PICTURE EP

    where (e.BRANCH_ID=b.BRANCH_ID)and
   

    (e.EMPLOYEE_ID=EP.EMPLOYEE_ID) and

    (b.COUNTRY_ID=@countryid or ISNULL(@countryid, -1) = -1 ) and
    (b.CITY_ID=@cityid or ISNULL(@cityid, -1) = -1 ) and
    (e.BRANCH_ID=@branchid or ISNULL(@branchid, -1) = -1 ) AND
    (e.DEPARTMENT_ID=@deptid or ISNULL(@deptid, -1) = -1)

END

当我执行该存储过程时，它会提供正确的信息.


但我在按钮单击事件中在代码中使用了它.

this stored procedure is giving correct info when i am executing it.


but i used it in my code like this in button click event.

DataSet dsserachemp = new DataSet();
      string countryid = Ddlcountry.SelectedValue.ToString();
      string cityid = DdlCity.SelectedValue.ToString();
      string branchid= DdlBranch.SelectedValue.ToString();
      string deptid = DdlDepartment.SelectedValue.ToString();
      dsserachemp = bl.BsearchEmp(countryid, cityid, branchid, deptid);
      GridView1.DataSource = dsserachemp;
      GridView1.DataBind();

public DataSet BsearchEmp(string countryid, string cityid, string branchid, string deptid)
   {
       return Dl.searchemp(countryid, cityid, branchid, deptid);
   }

public DataSet searchemp(string countryid, string cityid,string branchid,string deptid)
   {
       if (cn.State.Equals(ConnectionState.Open)) cn.Close();

       cn.Open();
       cmd = new SqlCommand("SearchEmployee", cn);
       cmd.Parameters.AddWithValue("@countryid", countryid);
       cmd.Parameters.AddWithValue("@cityid", cityid);
       cmd.Parameters.AddWithValue("@branchid", branchid);
       cmd.Parameters.AddWithValue("@deptid", deptid);
       da = new SqlDataAdapter(cmd);
       DataSet dssearch = new DataSet();
       da.Fill(dssearch);
       return dssearch;

比，而在执行

此代码无法提供正确的结果，即使我更改部门或分支机构的值(存储过程运行正常)，它也会显示所有记录.

请帮忙....

[PO''H-编辑过的帖子，因为它破坏了网站首页的布局]

than, while executing

This code it is not giving proper result, it is showing all the records even if i change the value for department or branch( stored procedure is working fine) .

Please Help....

[PO''H - edited post because it was breaking the layout of the site homepage]

推荐答案

请尝试在所有where子句中使用COALESCE .这是一个示例:

Try using the COALESCE in all your where clause. here is one example:

[b.COUNTRY_ID] = COALESCE(@countryid, [b.COUNTRY_ID])

注意:发布单独的解决方案以避免混乱.

OK，让我们看看如何做到这一点.

假设您有一个国家/地区的下拉列表，其中项目为All, India, Denmark ....，值为0,1,2.......

将sqlparameter填充修改为:

Note: Posting separate solution to avoid cluter.

OK lets see how this can be done.

Lets say you have the dropdown for country with items as All, India, Denmark .... with values 0,1,2.......

Modify the sqlparameter population as:

if(countryid == 0)
{
 cmd.Parameters.AddWithValue("@countryid", DBnull.Value);
}
else
{
 cmd.Parameters.AddWithValue("@countryid", countryid);
}

所有值都将由三元运算符检查.此外，此解决方案将与我以前的解决方案一起使用，即您必须使用COALESCE operator.(它也可以与您的SP一起使用，但我尚未对其进行测试)

The value for all will come under the check of ternary operator. Also, this solution will work along with my previous solution i.e. you will have to use COALESCE operator.(it could work with your SP too but i have not tested it)

这篇关于使用存储过程时出现问题，请帮忙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！