ASP.NET - sp

Asked By Neethu on 13-Feb-12 06:36 AM
hi all,

I have 3 dropdownlist , ddlseverity, ddlpriority & ddlstatus  and one button "submit"

ddlseverity contains items - Low, medium, High , veryhigh
ddlpriority             - Low, medium, High , veryhigh
ddlstatus             - New, Open, Reopen, Fixed..........................


if  ddlseverity selecteitem is "high", only issue id with high severity should display in datagrid
if  ddlseverity selecteitem is "medium" and status selecteditem is "open", only issueid with medium severity and status open should display.

I need the query for writing the stored procedure in this way using if statement

Sandeep Mittal replied to Neethu on 13-Feb-12 06:45 AM
Create Stored procedure something like this. Pass null if selected <--ALL--> for all parameters respectively

CREATE PROC procname
  @pSeverity VARCHAR(10), @pPriority varchar(10), @pStatus varchar(100
AS
BEGIN
  --select statement
  WHERE   (severity = @pSeverity or @pSeverity IS NULL)
  AND   (severity = @pPriority or @pPriority IS NULL)
  AND   (@status = @pStatus or @pStatus IS NULL)
END
dipa ahuja replied to Neethu on 13-Feb-12 06:45 AM
you have create sp with where condition
 
ALTER PROCEDURE dbo.sp1
 (
   @id int
)
    
AS
    /* SET NOCOUNT ON */

SELECT * FROM table1 WHERE id = @id


RETURN
 
 
protected void Button1_Click(object sender, EventArgs e)
{
  string conn = "ConnectionString";
 
  SqlConnection sqlcon = new SqlConnection(conn);
 
  sqlcon.Open();
 
  SqlCommand comm = new SqlCommand("sp1", sqlcon);
  comm.CommandType = CommandType.StoredProcedure;
 
  comm.Parameters.AddWithValue("id", int.Parse(txtid.Text.ToString()));  
  comm.ExecuteNonQuery();
}
 
 
hope this will help you!!
 
 
 
 
C D replied to Neethu on 13-Feb-12 06:50 AM

Hello,

 

Try your SP as following way

 

You need to crate SP Dynemicaly.

CREATE PROCEDURE [dbo].[BindSPResult]

(

    @severity VARCHAR(50) = '',

    @priority VARCHAR(50) = '',

    @status VARCHAR(50) = ''

)

AS

 

   

 

BEGIN

    DECLARE @Query AS VARCHAR(MAX)

    SET @Query = ' select * from TableName where 1 = 1 '

   

    IF (@severity <> '')

    BEGIN

        set @Query =  @Query + ' and severity  =''' + CONVERT(VARCHAR, @severity)+''''

    END

   

    IF (@priority <> '')

    BEGIN

        set @Query =  @Query + ' and priority  =''' + CONVERT(VARCHAR, @priority)+''''

    END

   

    IF (@status <> '')

    BEGIN

        set @Query =  @Query + ' and status  =''' + CONVERT(VARCHAR, @status)+''''

    END

   

    EXEC(@Query)

   

END

 

 

Hope this is helpful !

Thanks

 

 

 

 

 

Web Star replied to Neethu on 13-Feb-12 06:56 AM
you simply pass the all three selected id from dropdonwlist as paramters of stored proc and than write query based on that as follows
you also make sure pass 0 value corresponding to dropdownlist if no selection in dropdownlist for "Select any one" value.
See this below sp cover each and every combination for all three selection

Create proc spName
(
@Severity int,
@Periority int,
@Status int
)
AS
Begin
-- here you can make valid query based on your condition if that not selected 0 index in dropdownlist
if(@Severity > 0 AND @Periority  > 0 AND @Status  > 0 )
Begin
Select * from tblname where Severity  =@Severity  AND Periority   =@Periority    AND Status  =@Status   
 End
 if(@Severity > 0 AND @Periority  > 0  )
Begin
Select * from tblname where Severity  =@Severity  AND Periority   =@Periority     
 End
Else if(@Severity > 0  AND @Status  > 0 )
Begin
Select * from tblname where Severity  =@Severity  AND Status  =@Status   
 End 
 Else if(@Periority  > 0 AND @Status  > 0 )
Begin
Select * from tblname where Periority   =@Periority   AND Status  =@Status   
 End  
 Else if(@Severity > 0)
Begin
Select * from tblname where Severity  =@Severity  
End
Else if(@Periority  > 0)
Begin 
Select * from tblname where Periority   =@Periority   
 End
Else if(@Status  > 0)
Begin 
Select * from tblname where Status  =@Status  
 End 



End