如果其他问题，嵌套的SQL Server服务器.. [英] Sql server nested if else problem..

问题描述

我正在尝试根据下拉列表中选择的项目过滤gridview。

我在cs中有一些错误，在程序中添加参数，在我的sql server中我有一些查询问题。它返回更多行。它是添加所有行，并在下拉列表中选择，我猜我的查询是错误的...例如totaly我有109条记录。如果我选择在下拉列表中分配了类似24.所以总共24和109添加并返回133.





请帮助我查询??



和向cmd添加参数时出现问题。



我有什么试过：



我的程序..

I am trying to filter gridview based on the items selected in dropdown list .
i have some errors in the cs like, add parameters to the procedure and in my sql server i have some query problem. its returning more rows. it is adding all rows and which ever is selected in dropdown i guess my query is wrong.. for example totaly i have 109 records. and if i select assigned in dropdown have like 24. so total 24 and 109 are added and 133 is returned.


please help me with query ??

and problem while adding parameters to cmd.

What I have tried:

My procedure..

alter procedure Sp_SelectedStatus 'Assigned'
@Status varchar(20)
as 
Begin
      
    IF (@Status = 'Active')
    BEGIN
        SELECT A.GroupName, 
               B.Brand, 
               B.Model, 
               B.SerialNo, 
               B.Status,
               B.AddedOn 
        FROM   GroupDetails AS A
        INNER JOIN DeviceDetails AS B ON A.GroupId = B.GroupId 
        WHERE  Status = 'Active'
    END

    IF (@Status = 'Assigned')
    BEGIN
        SELECT A.GroupName, 
               B.Brand, 
               B.Model, 
               B.SerialNo,
               B.Status,
               B.AddedOn 
        FROM   GroupDetails AS A
        INNER JOIN DeviceDetails AS B ON A.GroupId = B.GroupId 
        WHERE  Status = 'Assigned'
    END

    IF (@Status = 'Returned')
    BEGIN
        SELECT A.GroupName, 
               B.Brand, 
               B.Model, 
               B.SerialNo, 
               B.Status,
               B.AddedOn 
        FROM  GroupDetails AS A
        INNER JOIN DeviceDetails AS B ON A.GroupId = B.GroupId 
        WHERE Status = 'Returned'
    END
    if (@Status = 'De-activated')
    Begin
        Select A.GroupName, 
               B.Brand , 
               B.Model , 
               B.SerialNo , 
               B.Status ,
               B.AddedOn 
        from GroupDetails as A
        inner join DeviceDetails as B on A.GroupId = B.GroupId 
        where Status = 'De-activated'
    End 

    if(@Status = 'Sent for repair')
    Begin
        Select A.GroupName , 
               B.Brand , 
               B.Model , 
               B.SerialNo , 
               B.Status ,
               B.AddedOn 
        from GroupDetails as A
        inner join DeviceDetails as B on A.GroupId = B.GroupId 
        where Status = 'Sent for repair'
    End
    else 
    Begin
        Select A.GroupName , 
               B.Brand , 
               B.Model , 
               B.SerialNo , 
               B.Status ,
               B.AddedOn 
        from GroupDetails as A
        inner join DeviceDetails as B on A.GroupId = B.GroupId
    End
End     

我的索引更改事件

my index changed event

protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(cs);
    SqlCommand cmd = new SqlCommand("Sp_SelectedStatus", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = "Status";
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();

    try
    {
        con.Open();
        da.Fill(ds);
        grdDeviceDetails.DataSource = ds;
        grdDeviceDetails.DataBind();
        con.Close();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
}

推荐答案

我在大约一天前给你答案。



这不是编写此sql查询的正确方法。你应该这样做：

I gave you your answer a day or so ago.

This is NOT the proper way to write this sql query. You should do it this way:

Select A.GroupName , 
       B.Brand , 
       B.Model , 
       B.SerialNo , 
       B.Status ,
       B.AddedOn 
FROM GroupDetails AS A
INNER JOIN DeviceDetails AS B ON A.GroupId = B.GroupId 
WHERE B.Status LIKE @status OR @status = ''

此外，如果是我，我会给@Status参数默认值为''。这样，我可以在没有参数的情况下调用proc并仍然返回记录。

这将返回与 @status ，或者如果@status是空字符串，则返回所有记录。此外，您应该在 WHERE 子句中使用 LIKE 而不是=，因为您不能保证这种情况将匹配。

Furthermore, if it were me, I'd give the @Status parameter a default value of ''. That way, I could call the proc with no parameters and still get records back.
This will return all records that match the current value of @status, or if @status is an empty string, returns all records. Furthermore, you should probably use LIKE instead of "=" in the WHERE clause because you can't guarantee the case will match.

这篇关于如果其他问题，嵌套的SQL Server服务器..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！