how to pass an optional parameter to sql function from C#

Question

I have an issue. please help to solve my problem

I have a SQL function

function [dbo].[fnKudishikaAmt]
    (@ParishName nvarchar(100), @Hno int, @dateto datetime = Null)
Returns Decimal(15,2)

This function shows proper result by using the execute command

Select dbo.fnKudishikaAmt('St.George Malankara Catholic Church', 29, default)

My requirement is this function should be called from C#

I am getting the error

Conversion failed when converting datetime from character string

Code:

public double kudishikatotal(string ParishName, Int32 HouseNo)
{
    String SQLText = "select ChurchDB.dbo.fnKudishikaAmt(@ParishName, @Hno, @dateto) as fnresult";

    SqlCommand cmd = new SqlCommand(SQLText);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@ParishName", ParishName);
    cmd.Parameters.AddWithValue("@Hno", HouseNo);
    cmd.Parameters.AddWithValue("@dateto", "default");

    string rval = GetSingleValue(cmd);
    double kudiamt = 0;

    if (rval != null)
    {
        kudiamt = Convert.ToDouble(rval);
    }

    return kudiamt;
}

private static string GetSingleValue(SqlCommand cmd)
{
    string ConString = connectionstring();
    string returnvalue = "";

    using (SqlConnection con = new SqlConnection(ConString))
    {
        cmd.Connection = con;
        con.Open();
        returnvalue = cmd.ExecuteScalar().ToString();
        con.Close();
    }

    return returnvalue;
}

Don't pass that parameter at all. Don't even add it to the parameters collection — Yuriy Galanter, Sep 25 '14 at 14:44
Same as http://stackoverflow.com/questions/8358315/t-sql-function-with-default-parameters ... but you should show your function definition. Is that second parameter a nullable DateTime type? — Mashton, Sep 25 '14 at 14:49
if i didn't include the date parameter i get the error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@dateto". — Jinesh Sam, Sep 25 '14 at 14:59

score 3 · Answer 1 · answered Sep 25 '14 at 14:44

3

If you've declared default value for parameter in stored procedure - then you can just not pass this parameter from c# code at all, and in this case it will have default value.

In your case exception thrown because it's impossible to convert string "default" to SqlDateTime which is your parameter type.

answered Sep 25 '14 at 14:44

Andrey Korneyev

26,353
15
70
71

if we are using stored procedure we can avoid the default parameter but in my case i am using function if i didn't include the date parameter i get the error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@dateto". – Jinesh Sam Sep 25 '14 at 14:56

score 0 · Answer 2 · answered Sep 25 '14 at 14:47

YOu can use if condition while sending the datetime parameter.

if(some condition)
{
 cmd.Parameters.AddWithValue("@dateto", dateTimeValue);
}

Here datetimeValue is the value you want to pass. So you will be passing dateTimeValue only if required. The error is due to the string "default" you passed.

how to pass an optional parameter to sql function from C#

2 Answers2