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
