I am using ef4.0 I have a complex and big sp that return a data when normally used with enterprise library, but doesn't return any data when using with ef.
here is the SP:
CREATE PROCEDURE [dbo].[usp_SelectLocations_NotEmptyForTreeView]
@RefrencePage int,
@JobId int,
@ItemName varchar(max),
@IsInRush bit,
@ItemRoomId int,
@SearchLocationId int,
@ItemCategoryId int,
@ItemProductionStatusId int,
@ItemStatusId int,
@ShowAll bit
AS
BEGIN
SET NOCOUNT ON;
DECLARE @JobItemStatusIdList nvarchar(max),
@JobItemProductionStatusIdList nvarchar(max),
@IsStorageLoc bit,
@IsDepartmentFilterChecked bit
if (@RefrencePage = 6)
begin
if(@ShowAll = 1)
set @JobItemProductionStatusIdList = N'1,57,58,62'
else
begin
set @JobItemProductionStatusIdList = N'1,57,62'
set @JobItemStatusIdList = N',30'
end
end
else if (@RefrencePage = 3)
begin
if(@ShowAll = 1)
set @JobItemProductionStatusIdList = N'54,58,59,63,69,70'
else
begin
set @JobItemProductionStatusIdList = N'54,58,59,63,69'
set @JobItemStatusIdList = ''
set @IsStorageLoc = 0
end
end
else if (@RefrencePage = 7)
begin
if(@ShowAll = 1)
begin
set @JobItemProductionStatusIdList = N'70,55,84'
set @JobItemStatusIdList = N'26,27,28,29,60,67,68,75,15,16,17'
set @IsStorageLoc = 0
set @IsDepartmentFilterChecked = 1
end
else
set @JobItemProductionStatusIdList = N'70,55,84'
end
else if (@RefrencePage = 4 OR @RefrencePage = 13)
begin
set @JobItemProductionStatusIdList = N'75'
set @JobItemStatusIdList = N'67,26'
end
else if (@RefrencePage = 8)
begin
set @JobItemProductionStatusIdList = N'84'
set @JobItemStatusIdList = N'15,33'
end
else if (@RefrencePage = 11)
begin
set @JobItemProductionStatusIdList = N'57,62,70,75,84'
set @JobItemStatusIdList = N'66,65,11'
end
else if (@RefrencePage =12)
begin
set @JobItemProductionStatusIdList = N'58,59,63,70,75,84'
set @JobItemStatusIdList = N'21,19,20,61,25,18'
end;
DECLARE @TempL TABLE(LocationId int, ItemId int, ParentLocationId int, Name varchar(max), NameBarcode varchar(max), IsLocation Bit, JobItemStatusId int, JobItemProductionStatusId int, IsRush bit);
WITH Loc(LocationId, ParentLocationId, LocationName, LocationNameBarcode) AS
(
SELECT DISTINCT Locations.LocationId, Locations.ParentLocationId, Locations.LocationName,
ISNULL(Locations.LocationName, '') + ' {' + ISNULL(Locations.LocationBarcode, '') + '}'
FROM Locations INNER JOIN JobItems ON Locations.LocationId = JobItems.LocationId
WHERE
charindex(','+rtrim(cast(JobItemProductionStatusId as nvarchar(max)))+',',','+Coalesce(@JobItemProductionStatusIdList,cast(JobItemProductionStatusId as nvarchar(max)),'') +',') > 0
and charIndex(',' + rtrim(isnull(cast(JobItemStatusId as nvarchar(max)),'')) + ',',',' + Coalesce(@JobItemStatusIdList,isnull(cast(JobItemStatusId as nvarchar(max)),''),'') +',') > 0
AND isnull(ItemName,'') like '%' + REPLACE(REPLACE(isnull(@ItemName,''), '[', ''), ']', '') + '%'
and isnull(ItemRoomId,0) = coalesce(@ItemRoomId,ItemRoomId,0)
and ItemCategoryId = ISNULL(@ItemCategoryId,ItemCategoryId)
and ISNULL(Locations.LocationId, 0) = coalesce(@SearchLocationId, Locations.LocationId, 0)
and ItemCategoryId not in(5, 7)
and isnull(JobId,0) = Coalesce(@JobId,JobId,0)
and isnull(IsInRush,0) = Coalesce(@IsInRush,IsInRush,0)
AND IsStorageLocation = ISNULL(@IsStorageLoc, IsStorageLocation)
AND ISNULL(JobItemProductionStatusId, 0) != 44
AND ISNULL(JobItemProductionStatusId, 0) = ISNULL(@ItemProductionStatusId, JobItemProductionStatusId)
AND ISNULL(JobItemStatusId, 0) != 86
AND ISNULL(JobItemStatusId, 0) = Coalesce(@ItemStatusId, JobItemStatusId, 0)
AND ((@IsDepartmentFilterChecked = 1 AND ISNULL(Locations.LocationTypeId, 0) != 6) OR (@IsDepartmentFilterChecked IS NULL))
UNION ALL
SELECT cL.LocationId, cL.ParentLocationId, cL.LocationName,
ISNULL(cL.LocationName, '') + ' {' + ISNULL(cL.LocationBarcode, '') + '}'
FROM Locations AS cL
INNER JOIN Loc AS pL
ON cL.LocationId = pL.ParentLocationId
)
INSERT INTO @TempL(LocationId, ParentLocationId, Name, NameBarcode, IsLocation)
(
Select DISTINCT Loc.LocationId, Loc.ParentLocationId, Loc.LocationName, Loc.LocationNameBarcode, 1
FROM Loc
)
SELECT LocationId, ItemId, ParentLocationId, NameBarcode, IsLocation, JobItemStatusId, JobItemProductionStatusId, IsRush
FROM @TempL tl
where ParentLocationId is null or ParentLocationId=0
order by tl.Name
END
I have add this Sp as function and also have created the complex type for this.
Please help me to get out of this.
Thanks in advance.