locked
how can I call the sqlserver function RRS feed

  • Question

  • User-1355965324 posted

    I am trying to call the function to get the return value . Please any help would be very appreciated.

    My sqlserver function is given below

    ALTER  Function [dbo].[goCalculateMissingAttendance]
    (@Depot int, @Department int, @AttendanceDate  smalldatetime)
    RETURNS datetime
    AS 
    BEGIN
    DECLARE @VarMins decimal(5,2)
    DECLARE @BreakDeduct decimal(5,2)
    declare @Missdate Datetime
    
    Select @Missdate = GETDATE() 
    RETURN @Missdate  ;
    END
    
    

    In My Application. I am calling stored procedure. But I want to call function  goCalculateMissingAttendance  rather than stored procedure. I dont know how declare the function in sqlhelper.cs and also how to call the function from sqlhelper  in the place 

    public static string checkmissingAttendance(SqlConnection tranEmployee, int Depot, int Department, DateTime AttendanceDate)
            {
    
                datetime missdate  
    
                try
                {
                    SqlParameter parDepotNo = new SqlParameter("@Depot", Depot);
                    SqlParameter parDepartment = new SqlParameter("@Department", Department);
                    SqlParameter parAttendanceDate = new SqlParameter("@AttendanceDate", AttendanceDate);
    
    
    
    
    
    
    
                    missdate = (string)SqlHelper.ExecuteScalar(tranEmployee
                                                            , CommandType.StoredProcedure
                                                            , StoredProcedureConstants.goCalculateMissingAttendance // Error is coming. I dont know how call the function
                                                            , MTSGOConstants.DEFAULT_COMMAND_TIME_OUT
                                                            , parDepotNo
                                                            , parDepartment
                                                            , parAttendanceDate
                                                            );
                }
                catch (Exception ex)  // Error message is coming here  Conversion failed when converting the varchar value '2019-05-31' to data type int
                {
                    throw ex;
                }
    
                return missdate;
            }

    In storedprocedureconstant.cs

    public const string GET_SYSTEM_CALCULATED_HOLIDAY = "dbo.goCalculateMissingAttendance";

    With Many Thanks

    Pol

    Thursday, June 13, 2019 7:34 PM

Answers

  • User-37275327 posted

    You can't call a function, Instead write an inline SQL statement

    'Select dbo.goCalculateMissingAttendance'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 14, 2019 3:23 AM
  • User-474980206 posted

    just minor change to your code, its a sql query and you select the function:

    SqlParameter parDepotNo = new SqlParameter("@Depot", Depot);
    SqlParameter parDepartment = new SqlParameter("@Department", Department);
    SqlParameter parAttendanceDate = new SqlParameter("@AttendanceDate", AttendanceDate);
    
    var sql = $"select {StoredProcedureConstants.goCalculateMissingAttendance}(@Depot,@Department,@AttendanceDate) as theValue";
    missdate = (string)SqlHelper.ExecuteScalar(tranEmployee
        , CommandType.Text
        , sql 
        , MTSGOConstants.DEFAULT_COMMAND_TIME_OUT
        , parDepotNo
        , parDepartment
        , parAttendanceDate
    );
       

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 14, 2019 3:06 PM

All replies

  • User-37275327 posted

    You can't call a function, Instead write an inline SQL statement

    'Select dbo.goCalculateMissingAttendance'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 14, 2019 3:23 AM
  • User-854763662 posted

    Hi polachan ,

    You could try to use a raw SQL command as the starting point for a query

    https://docs.efproject.net/en/latest/querying/raw-sql.html.

    https://www.learnentityframeworkcore.com/raw-sql

    Best Regards ,

    Sherry

    Friday, June 14, 2019 8:59 AM
  • User-474980206 posted

    just minor change to your code, its a sql query and you select the function:

    SqlParameter parDepotNo = new SqlParameter("@Depot", Depot);
    SqlParameter parDepartment = new SqlParameter("@Department", Department);
    SqlParameter parAttendanceDate = new SqlParameter("@AttendanceDate", AttendanceDate);
    
    var sql = $"select {StoredProcedureConstants.goCalculateMissingAttendance}(@Depot,@Department,@AttendanceDate) as theValue";
    missdate = (string)SqlHelper.ExecuteScalar(tranEmployee
        , CommandType.Text
        , sql 
        , MTSGOConstants.DEFAULT_COMMAND_TIME_OUT
        , parDepotNo
        , parDepartment
        , parAttendanceDate
    );
       

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 14, 2019 3:06 PM