How to Pass Dynamic Parameter

Question

I have two Parameter in Sql Function (StartDate and EndDate).I am passing the input Parameter from C# Code.When I Pass two Parameter,the Query result working fine.My question is, How to Pass only "EndDate" no "Start Date" (Start Date is Empty),So the Query result should be all record till "EndDate"

Sql Query:

Select * from FnEmployeeProduction(?,?)

I think you may need to pass **`null`** and need to do appropriate validation in your `UDF`. — Ullas, Apr 23 '18 at 12:57
You may need to send the dbnull if you dont have a proper value — BugFinder, Apr 23 '18 at 12:59
It depends on how you are processing the dates in your function. You could declare the parameters as default NULL, or declare default values (e.g. '1900-01-01 00:00:00.000')... or a combination of both? — DimUser, Apr 23 '18 at 13:04
`Select * from FnEmployeeProduction(somevalue, null)`and handle the possiblity for null in the funtion — GuidoG, Apr 23 '18 at 13:09

GuidoG · Answer 1 · 2018-04-23T14:33:36.700

1

you need to send NULL to the database, but for some reason microsoft has invented DBNull.Valuein c#

Here is an example of calling your function in c#

DateTime? beginDate = null;
DateTime? endDate = DateTime.now;

using (SqlCommand command = new SqlCommand("select * from FnEmployeeProduction (@begin, @end)");
{  
    command.Parameters.Add("@begin", SqlDbType.DateTime).Value = (beginDate == null) ? (object)DBNull.Value : beginDate;
    command.Parameters.Add("@end", SqlDbType.DateTime).Value = (endDate == null) ? (object)DBNull.Value : endDate;

    // now you can use this command object to send to your DB
    using (SqlConnection connection = new SqlConnection(your conn string))
    {  
       connection.Open();
       command.Connection = connection;
       using (SqlDataAdapter adapter = new SqlDataAdapter(command))
       {
           adapter.Fill(your datatable);
       }
    }
}

And inside your function you need to handle null values off course

create function FnEmpoyeeProduction (@begin datetime, @end datetime) as
begin
     if @begin is not null 
     begin
     end
     and so on...
end

edited Apr 23 '18 at 14:33

answered Apr 23 '18 at 13:57

GuidoG

11,359
6
44
79

1

This is the "idiot reason": There's a big conceptual difference between `DbNull.Value` and `null`. The keyword `null` represents an invalid reference. The class `System.DbNull` represents a nonexistent value in a database field. – ˈvɔlə Apr 23 '18 at 14:21
@GuidoG 'idiotic reason'? First read about the difference here, then you can correct your statement: https://stackoverflow.com/questions/4958379/what-is-the-difference-between-null-and-system-dbnull-value – L-Four Apr 23 '18 at 14:30
@WoIIe I know the difference, but there still is no reason to create a DBNull.Value in c# the compiler could easily know the difference from the code – GuidoG Apr 23 '18 at 14:34
@L-Four From the other anwser in your link its clear I am not the only one that thinks DBNull.Value is annoying. But ok I removed the idiot word in my answer there is no need for discussions about this – GuidoG Apr 23 '18 at 14:36

How to Pass Dynamic Parameter

1 Answers1