Calling UDF in ASP.NET

Question

I am a newbie in ASP.NET, having trouble in how to call an inline User Defined Function in my ASP.NET web application.

Here, I have passed two arguments in my function - one is available leave(lv) and another one is duration (dr). I am simply subtracting dr from lv and returning the value. But I am having problem in calling the function.

I have tried "SELECT dbo.emp_leave_sub(lv,dr) as remaining" instead of the query "SELECT dbo.emp_leave_sub(lv,dr) FROM Employee1 where Employee1.emp_id='" + emp_id + "'" but it didn't work. I can not understand what I am doing wrong.

Looking forward to your kind reply. Any help will be highly appreciated.

Below is my function :

    ALTER FUNCTION dbo.emp_leave_sub(@available int, @duration int)
  RETURNS int
  AS
  -- Returns the availabe leave after deduction for the employee.
  BEGIN
  DECLARE @ret int;
  SELECT @ret = @available - @duration;
  RETURN @ret;
  END;


And this is from where I am calling my function :

    try
            {
                SqlDataReader rdr;
                SqlConnection conn = new SqlConnection (ConfigurationManager.
                ConnectionStrings["PMSConnectionString"].ConnectionString);
                conn.Open();

                string sub_leave = "SELECT dbo.emp_leave_sub(lv,dr) FROM       `  `               Employee1 where Employee1.emp_id='" + emp_id + "'";
                SqlCommand com2 = new SqlCommand(sub_leave, conn);

                com2.CommandType = CommandType.Text;

                using (conn)
                {
                    //read data from the table to our data reader
                    rdr = com2.ExecuteReader();

                    //loop through each row we have read
                    while (rdr.Read())
                    {
                        remaining = rdr.GetInt32(0);
                    }
                rdr.Close();
            }

Is the 'lv' and 'dr' columns declared on the Employee1 table? Also, please don't inject the emp_id variable directly into your SQL statement, use a Parameter, as (now) in @thewisegod's answer. — , Sep 08 '15 at 00:59
@Will Hughes - hi, no, they are not the columns of my Employee1 table. They are variables where I kept values two values that I got from the Employee1. — user3325349, Sep 09 '15 at 13:48

score 1 · Accepted Answer · edited Sep 08 '15 at 00:55

Try to do this:

SqlDataReader rdr;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PMSConnectionString"].ConnectionString))
    {
            conn.Open();

            string sub_leave = "SELECT dbo.emp_leave_sub(@available,@duration) FROM Employee1 where Employee1.emp_id=@empid";
            SqlCommand com2 = new SqlCommand(sub_leave, conn);
            com2.Parameters.AddWithValue("@available", your value);
            com2.Parameters.AddWithValue("@duration", your value);
            com2.Parameters.AddWithValue("@empid", emp_id);
            com2.CommandType = CommandType.Text;

               //read data from the table to our data reader
               rdr = com2.ExecuteReader();
             //loop through each row we have read
               while (rdr.Read())
                {
                     remaining = rdr.GetInt32(0);
                }
    }
    rdr.Close();

Updated the answer: If you're going to declare variables, you need to use them in the statement. Also, emp_id should've been a parameter and not injected into the SQL statement like that (yeah, I know it was in the original question, no reason not to update the statement to fix it though). — , Sep 08 '15 at 00:57
Hi,,,this worked... :) :) though I had to change "@available" and "@duration" into the column names of Employee1 table. otherwise the UDF was returning 0. Anyway, Thanks a lot... :) :) — user3325349, Sep 09 '15 at 14:28

Calling UDF in ASP.NET

1 Answers1