Click here to Skip to main content
15,889,315 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,

Hii , 


ALTER  PROCEDURE [dbo].[usp_Get121ReportForMasterAdmin] 
(  
 @DataForYear   Int,  
 @CompanyId    Int,  
 @ManagerId    Int,  
 @BusinessUnitId   Int,  
 @DepartmentId   Int,  
 @TeamId     Int   
)  
As  
Begin  
  
  if (@BusinessUnitId <= 0)  
  begin  
  set @DepartmentId = 0  
  set @TeamId = 0  
  end  
  
  if (@DepartmentId <= 0)  
  begin  
  set @TeamId = 0  
  end  
  
  -- get company business year period  
  DECLARE @businessYearStart  as date  
  DECLARE @businessYearEnd as date  
  exec usp_GetCompanyBusinessYearPeriod   
   @compID = @CompanyId,  
   @DataForYear = @DataForYear,  
   @businessYear_end = @businessYearEnd OUTPUT,  
   @businessYear_start = @businessYearStart OUTPUT  
    
  
  Declare @IncludeTestEmployeeInReport bit  
  Declare @IncludeHRAdminInReport bit  
  
  set @IncludeTestEmployeeInReport=isnull((Select IncludeTestEmployeeInReport from Company where id=@CompanyId),0)  
  set @IncludeHRAdminInReport=isnull((Select IncludeHRAdminInReport from Company where id=@CompanyId),0) 
  
  declare @finalTable as table
	(
		rowID				int identity(1, 1),
		EmpId				int,
		Companyname   nvarchar(max),
		PlanSubject     nvarchar(max),
		PlanType        nvarchar(50),
		PlanDate        datetime,
		[Status]         nvarchar(20),
		EmployeeName  nvarchar(50),
		ManagerName  nvarchar(50)
		
	) 
  insert into @finalTable
  Select
	EmpId,
   (Select Name from Company where Id = CompanyId) Companyname,PlanSubject,PlanType,PlanDate,[Status],EmployeeName,  
  ManagerName From  
  (  
  Select e.Id as EmpId ,p.Comp_Id CompanyId,  
  (Select p.Plan_Subject) as PlanSubject,  
  (Select Case p.[121Type] when  1 then 'Virtual' when  2 then 'Face to Face' Else '' end) as PlanType,  
  (Select p.Paln_Date) as PlanDate,  
  (Select   Case p.IsClosed when  0 then 'Planned' when  1 then 'Published' Else '' end) as  [Status],  
  (Select e.LastName + ' ' + e.FirstName) EmployeeName,  
  
  (Select   
  case when ((Select count(*) from CurrentManagerLogDetails where EmployeeId=e.Id and DataForYear=@DataForYear)=0)  
  then  
  (Select LastName + ' ' + FirstName from Employee where Id = e.ManagerId)  
  else  
  (Select LastName + ' ' + FirstName from Employee where Id = (Select CurrentManagerId from CurrentManagerLogDetails   
  where EmployeeId=e.Id and DataForYear=@DataForYear))  
  end) ManagerName  
    
  from [Plan] p   
  inner join Employee e     
  on p.PlanAddedFor_Id = e.Id  
  where p.Comp_Id = Company_Id    
  and p.IsDeleted=0  
  and p.Comp_Id = @CompanyId  
  and e.Status_Id=0  
  and ((@IncludeTestEmployeeInReport <> 1) and (e.Id in (Select Id from Employee with (nolock)   
  where isnull(IsTest,0) <> 1 and Company_Id=@CompanyId)) or @IncludeTestEmployeeInReport=1)  
  and e.IsDeleted = 0  
  and ((@IncludeHRAdminInReport <> 1) and (e.Id in (Select Id from Employee with (nolock)  where Role_Id <> 4  
  and Company_Id=@CompanyId )) or @IncludeHRAdminInReport=1)  
  
  
  and isnull(p.Paln_Date, @businessYearStart) between @businessYearStart and @businessYearEnd  
    
  and ( isnull(@ManagerId,0)=0 or e.ManagerId = @ManagerId)  
  and (isnull(@BusinessUnitId,0)=0 or e.BusinessUnitId = @BusinessUnitId)  
  and (isnull(@DepartmentId,0)=0 or e.DepartmentId = @DepartmentId)  
  and (isnull(@TeamId,0)=0 or e.TeamId = @TeamId )  
    
  )f  
  order by ManagerName,EmployeeName  

  declare @totalRecords as int 
	set @totalRecords = (select COUNT(*) from @finalTable)
	if(@totalRecords > 0)
	begin
		declare @counter as int
		declare @tmpID as int
		declare @tmpMgrName as nvarchar(100)
		set @counter = 1
		while (@counter <= @totalRecords)
		begin
			set @tmpID = (select EmpId from @finalTable where rowID = @counter)
			EXEC usp_GetManagerNameByYear @empId = @tmpID, @compId	= @CompanyId, @dataForYear= @DataForYear, @returnManagerName = @tmpMgrName output
			update @finalTable set ManagerName = ISNULL(@tmpMgrName, ManagerName) where rowID = @counter and EmpId = @tmpID 
			set @counter = @counter + 1
		end
	end

	select

		Companyname  ,
		PlanSubject    ,
		PlanType       ,
		PlanDate       ,
		[Status]       ,
		EmployeeName,
		ManagerName 

	from @finalTable
	order by ManagerName, EmployeeName
   
End


GO


this is my procedure 


when i try to drag on drop on my linq orm it gives me an error that return type of the procedure could not be detected ..

Please suggest .. i tried to set autotdetect for this by going to properties of this procedure .. but not able to ..

Please suggest
Posted

1 solution

sometimes linq to sql is very unpredictable :- when u try to use tem table or when u try to return complex table structure ..many of the times linq to sql doesnt work with this type

after doing some research on internet , i found the below solution

SET FMTONLY OFF;


by using this commmand at the begining of storeprocedure linq to sql accept this procedure and makes that procedures return type as autodetect retyrn type
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900