0

I am able to execute the below query

SELECT [Dbo].[fn_get_date](GETDATE(), 7, 1, 1, 1)

I am trying to call the function from C#.

If I pass hard string, I am getting the same data. But when I am trying to pass the date as parameter, It is throwing error. What am I doing wrong.?

 public DateTime? get_settlement_date(DateTime startDate, int numberOfDays, string calendar)
    {
        using (var connection = new SqlConnection(_connection_string))
        {
            connection.Open();

            var query = "SELECT [Dbo].[fn_get_date] (GETDATE(), 7, 1, 1, 1)"; // This is executing fine

            query = $"SELECT [Dbo].[fn_get_date] ({startDate}, 1, 1, 1, 1)"; // This is throwing error

            var objCommand = new SqlCommand(query, connection);

            var settlement_Date = objCommand.ExecuteScalar();

            return (DateTime) settlement_Date;
        }
    }

I don't want to create another stored procedure here.

Chatra
  • 2,989
  • 7
  • 40
  • 73
  • 1
    "to pass the date as parameter" - please review your post and make sure you actually pasted correct code - there is no code showing how you add parameter to the query. – Alexei Levenkov Mar 24 '23 at 17:41
  • startDate is parameter, right ? – Chatra Mar 24 '23 at 17:43
  • 1
    `startDate` is indeed a parameter of `get_settlement_date` C# function... but it looks to me the question mostly concerned with passing parameters to SQL query - and that part is missing (unless you consider poor attempt to demonstrate SQL injection to be "pass a parameter" - which is indeed not). – Alexei Levenkov Mar 24 '23 at 17:52
  • 1
    startDate is being formatted into your query string as if it was a literal value. Literal strings and dates in SQL Query text need to be enclosed in single quotes. BTW this type of SQL construction is poor practice because of SQL injection vulnerability. The SQL function call works because it understands the function usage. – Zenilogix Mar 24 '23 at 17:53
  • @Zenilogix What single quotes, can you show me? – Chatra Mar 24 '23 at 18:01
  • 2
    @chatra forget about the quotes, see the duplicate for correct parameter usage – Hans Kesting Mar 24 '23 at 18:16

0 Answers0