0

Background: My store procedure 'GetAtdRecord_Sp' to optionally accept parameter 'EmplID' otherwise default value is null. It works in SQl server management studio even if i provide no value as i wanted to do.

exec GetAtdRecord_Sp     // This works 
exec GetAtdRecord_Sp 23 //  This works too with value.

Problem: But when i execute it from Asp.net MVC application via Linq to Sql code then it needs a parameter and throws error:

Error   1   No overload for method 'GetAtdRecord_Sp' takes 0 arguments

and works if i pass value like given below.

Code: using EmployeeAttendance_app.Models;

namespace EmployeeAttendance_app.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "Precise Technology Consultants";
            var DataContext = new EmployeeAtdDataContext();
            //var EmployeeAtd = DataContext.GetAttendance_Sp();
            IEnumerable<GetAtdRecord_SpResult> EmployeeAtd =  DataContext.GetAtdRecord_Sp(22).ToList();
            return View(EmployeeAtd);
        }

but it doesn't work when i leave GetAtdRecord_Sp() empty like this.

SP:

        ALTER PROCEDURE [dbo].[GetAtdRecord_Sp] 
        @EmplID INT = NULL
        AS
        BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;


    IF (@EmplID IS NULL) --IF block starts
    BEGIN
        WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,t1.RecDate AS [DateVisited]
            FROM AtdRecord t1
            INNER JOIN AtdRecord t2 ON t1.EmplID = t2.EmplID
                AND t1.RecDate = t2.RecDate
                AND t1.RecTime < t2.RecTime
            INNER JOIN HrEmployee t3 ON t3.EmplID = t1.EmplID
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )
        SELECT EmplID
            ,EmplName
            ,InTime
            ,[TimeOut]
            ,[DateVisited]
            ,CASE 
                WHEN minpart = 0
                    THEN CAST(hourpart AS NVARCHAR(200)) + ':00'
                ELSE CAST((hourpart - 1) AS NVARCHAR(200)) + ':' + CAST(minpart AS NVARCHAR(200))
                END AS 'total time'
        FROM (
            SELECT EmplID
                ,EmplName
                ,InTime
                ,[TimeOut]
                ,[DateVisited]
                ,DATEDIFF(Hour, InTime, [TimeOut]) AS hourpart
                ,DATEDIFF(minute, InTime, [TimeOut]) % 60 AS minpart
            FROM times
            ) source
    END --IF block ends
2
Contributors
1
Reply
10
Views
3 Years
Discussion Span
Last Post by pritaeas
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.