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