calling sql server stored dynamic parameters search using null values for datetime example

the following example idea is to create search form using c# used stored procedure dynamic parameters according to user input search Criteria.

the idea behind the example :
1- create Dynamic sqlParameter List to pass to stored procedure with optional Parameters.
2- Pass From and To Dates Dynamically to the Procedure so if user enter from date the search will handle begining date and no end date for query. and so on.
3- Handle automatic empty values to null parameters to pass query check for form fields.


Example:
1- DB Layer Function that will Bind Sql Parameter list to Procedure.

  public DataTable ReturnTableS(string Stored, List<SqlParameter> p)
    {

        cmd = new SqlCommand(Stored, cn);
        cmd.CommandType = CommandType.StoredProcedure;
        DataTable tbl = new DataTable();
        if (!p.Equals(null))
        {
            foreach( SqlParameter prm in p)
            {
                    cmd.Parameters.Add(prm);
            }
        }
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
        try
        {

            db.SelectCommand = cmd;
            db.Fill(tbl);
            if (cn.State == ConnectionState.Open) cn.Close();
        }
        catch (Exception ex)
        {
            if (cn.State == ConnectionState.Open) cn.Close();
            throw ex;
        }
        return tbl;
    }

2- C# function That Will Bind Parameters to dblayer function

  public DataTable VACATION_REPORTS_REQUEST_LOAD_SEARCH_ALL_REQUESTS(string REQUEST_REPORT_NO, string PATIENT_PROFILE_NO, string REQUEST_STAGE_ID, string REQUEST_STATUS_ID, string FROM_DATE, string TO_DATE)    {        List<SqlParameter> sqlparm = new List<SqlParameter>();        if (REQUEST_REPORT_NO.Length > 0)            sqlparm.Add(new SqlParameter("@REQUEST_REPORT_NO", REQUEST_REPORT_NO));
        if (PATIENT_PROFILE_NO.Length > 0)            sqlparm.Add(new SqlParameter("@PATIENT_PROFILE_NO", PATIENT_PROFILE_NO));                if (REQUEST_STAGE_ID.Length > 0)            sqlparm.Add(new SqlParameter("@REQUEST_STAGE_ID", REQUEST_STAGE_ID));                if (REQUEST_STATUS_ID.Length > 0)            sqlparm.Add(new SqlParameter("@REQUEST_STATUS_ID", REQUEST_STATUS_ID));
        if (FROM_DATE.Length > 0)            sqlparm.Add(new SqlParameter("@FROM_DATE", DateTime.ParseExact(FROM_DATE, "yyyy/MM/dd",enCulture.DateTimeFormat)));             if (TO_DATE.Length > 0)            sqlparm.Add(new SqlParameter("@TO_DATE", DateTime.ParseExact(TO_DATE, "yyyy/MM/dd", enCulture.DateTimeFormat)));
        DataTable Tbl_VACATION_REQUESTS = new DataTable();        Tbl_VACATION_REQUESTS = db.ReturnTableS("VACATION_REPORTS_REQUEST_LOAD_SEARCH_ALL_REQUESTS", sqlparm);        return Tbl_VACATION_REQUESTS;    }

3-Stored Procedure Code

create PROCEDURE [dbo].[VACATION_REPORTS_REQUEST_LOAD_SEARCH_ALL_REQUESTS]
@REQUEST_REPORT_NO VARCHAR(50) = NULL,
   @PATIENT_PROFILE_NO VARCHAR(50) = NULL,
    @REQUEST_STAGE_ID VARCHAR(50) = NULL ,
    @REQUEST_STATUS_ID VARCHAR(50) = NULL  ,
    @FROM_DATE DATETIME = NULL ,
    @TO_DATE DATETIME = NULL 
WITH EXEC AS CALLER
AS
SET NOCOUNT ON 
SET XACT_ABORT ON  
SELECT VR.*  
FROM VACATION_REPORTS_REQUEST VR
RIGHT JOIN WORK_FLOWS_TYPES_STAGES T ON VR.REQUEST_STAGE_ORDER = T.STAGE_ORDER
WHERE
        T.TYPE_ID = [DBO].[WORK_FLOWS_TYPE_GET_BY_USER_ID](6,VR.CREATE_BY_USER_ID)
  AND T.STAGE_ORDER =VR.REQUEST_STAGE_ORDER
  AND ([REQUEST_REPORT_NO] = @REQUEST_REPORT_NO OR @REQUEST_REPORT_NO IS NULL) 
  AND  (@PATIENT_PROFILE_NO IS NULL OR VR.PATIENT_PROFILE_NO = @PATIENT_PROFILE_NO)
  AND  (@REQUEST_STAGE_ID IS NULL  OR T.STAGE_ID = @REQUEST_STAGE_ID)
  AND  (@REQUEST_STATUS_ID IS NULL OR VR.REQUEST_STATUS_ID = @REQUEST_STATUS_ID)
  AND (@FROM_DATE IS NULL OR VR.CREATE_DATE > = @FROM_DATE)
  AND (@TO_DATE IS NULL OR VR.CREATE_DATE < = @TO_DATE)
  AND (@FROM_DATE IS NULL OR
(@TO_DATE IS NOT NULL AND @FROM_DATE IS NOT  NULL AND  VR.CREATE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
)
ORDER BY CREATE_DATE DESC