Sql server nested if else problem..

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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

C#

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);
    }
}

Posted 20-Feb-17 23:47pm

Mahesh2223

Updated 21-Feb-17 1:59am

#realJSOP

v3

Add a Solution

Comments

ZurdoDev 21-Feb-17 7:14am

You don't want to write your sql that way.
Instead, something like
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.GroupID = t2.GroupID
WHERE t1.Status = @Status OR @Status = ''

Then you have one single statement.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Accepted Answer · 2017-02-21T01:59:00

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:

SQL

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 = ''

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.