ASP.NET - How to Multiple NULL parameters in sql stored procedure

Asked By Bhanupratap Singh on 03-Mar-13 11:41 PM
How to pass null parameter and null value to stored procedure in sql
I want to get those records which match user input. Below I m passing paramter. Some parameters are nulll.
I m not able to get actual records from table . Where i m making mistake pls review it and give solutions
thanks

 SqlCommand cmdSEarchCnmtFind = new SqlCommand("sp_Cnmt_Finddd", con);
            cmdSEarchCnmtFind.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@sdate", SqlDbType.Date).Value = txtSDate;
            cmdSEarchCnmtFind.Parameters.AddWithValue("@edate", SqlDbType.Date).Value = txtEDate;//.ToString();//EndDatee.ToShortDateString();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@CODE_NAME", SqlDbType.Char).Value = ListBoxFrom.SelectedValue.ToString();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@TOO", SqlDbType.Char).Value = ListBoxToo.SelectedValue.ToString();
            
            cmdSEarchCnmtFind.Parameters.AddWithValue("@CONSIGNOR", SqlDbType.Char).Value = !string.IsNullOrEmpty(txtConsignor.Text) ? System.DBNull.Value.ToString() : txtConsignor.Text.ToString();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@CONSIGNEE", SqlDbType.Char).Value = !string.IsNullOrEmpty(txtConsignee.Text) ? txtConsignee.Text : null;
            cmdSEarchCnmtFind.Parameters.AddWithValue("@PVTMARKA", SqlDbType.Char).Value = !string.IsNullOrEmpty(txtPvt.Text) ? "NULL" : txtPvt.Text.ToString();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@NOPKG", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(txtPkg.Text.ToString()) ? System.DBNull.Value.ToString() : txtPkg.Text).ToString();
            cmdSEarchCnmtFind.Parameters.AddWithValue("@GOODS", SqlDbType.Char).Value = !string.IsNullOrEmpty(txtGoods.Text) ? System.DBNull.Value.ToString() : txtGoods.Text.ToString();
------------------------------
and My Procedure is below
------------------------------
ALTER PROCEDURE [dbo].[sp_Cnmt_Finddd]
(
@sdate as date
,@edate as date
    ,@CODE_NAME varchar(20)
    ,@TOO  as varchar (40)
     ,@CONSIGNOR as varchar(40)=null
    ,@CONSIGNEE as varchar (40)=null
    ,@PVTMARKA as VARCHAR (30)=null
    ,@NOPKG as varchar(6)=null
    ,@GOODS as varchar(30)=null
    
)
as
begin
select * from CONSIGNMENT
where CODE_NAME=@CODE_NAME and TOO=@TOO and (CNDT BETWEEN @sdate AND @edate) and
 
 (@CONSIGNOR  is null OR CONSIGNOR like '%'+@CONSIGNOR+'%') AND
 (@CONSIGNEE is null OR CONSIGNEE like '%'+@CONSIGNEE+'%')  AND
 (@PVTMARKA  is null OR PVTMARKA like '%'+@PVTMARKA+'%') AND
 (@NOPKG is null or NOPKG =@NOPKG) AND   //// this point is working but rest do not works
 (@GOODS  is null OR GOODS like '%'+@GOODS+'%') 
 End
Robbe Morris replied to Bhanupratap Singh on 04-Mar-13 10:39 AM
A wildcard LIKE statement needs to have the wildcards stored in the variable.

Your sql code for checking on null is wrong as well.  You'd want to check the column for null not the variable name.

Set @CONSIGNOR = '%' + @CONSIGNOR + '%'

and then use it like this:

where colA like @CONSIGNOR

Bhanupratap Singh replied to Robbe Morris on 05-Mar-13 12:04 AM
Hello robe
thanks for rply
pls elaborate it. I m not getting you.

how to set those parameter which are null. I have six parameter optional. user may put intput or may not. 
Pls complete my stored procedure with a example.

I have first 4 parameters compulsory  and rest are optional.
Pls complete this procedure for me.

thanks