Click here to Skip to main content
11,709,844 members (78,397 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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) ---- 6 For Restoration                                        
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) ---- 3 For Inspection                                        
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) --- 7 For Packaging                                      
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  -- show only not storage locations when "Packaging Required" is on  
  set @IsDepartmentFilterChecked = 1  
 end  
else  
 set @JobItemProductionStatusIdList = N'70,55,84'  
end                                      
else if (@RefrencePage = 4 OR @RefrencePage = 13)  --- 4 For Salvage Review                                      
begin                                       
  set @JobItemProductionStatusIdList = N'75'                                      
  set @JobItemStatusIdList = N'67,26'                                      
end                                      
else if (@RefrencePage = 8)  --- 8 For Waiting For Disposal                                      
begin                                      
   set @JobItemProductionStatusIdList = N'84'                                      
  set @JobItemStatusIdList = N'15,33'                                      
end                                      
else if (@RefrencePage = 11)   ---- 11 For Restoration Admin Approval                                      
begin                                      
   set @JobItemProductionStatusIdList = N'57,62,70,75,84'                                      
   set @JobItemStatusIdList = N'66,65,11'                                
end                  
else if (@RefrencePage =12)  ---- 12 For Inspection Admin Approval                                      
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  '%' + coalesce(@ItemName,ItemName,'') + '%' ESCAPE '\'  
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(LocationBarcode,'') = Coalesce (@LocationBarcode,LocationBarcode,'')                   
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   
        --WHERE cL.ParentLocationId = ISNULL(@SearchLocationId, cL.ParentLocationId)  
        --AND (@IsOnlyRoot = 1 AND cL.ParentLocationId IS NULL)  
--    WHERE Level = 0  
)  
--insert locations  
INSERT INTO @TempL(LocationId, ParentLocationId, Name, NameBarcode, IsLocation)  
(  
 Select DISTINCT Loc.LocationId, Loc.ParentLocationId, Loc.LocationName, Loc.LocationNameBarcode, 1   
 FROM Loc   
 -- where Loc.ParentLocationId IS NULL  
 --WHERE Level = 0  
 --WHERE ISNULL(Loc.ParentLocationId, 0) = COALESCE(@SearchLocationId, Loc.ParentLocationId, 0)  
 --OR (@IsOnlyRoot = 1 AND Loc.ParentLocationId IS NULL)  
)  
 
  
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.
Posted 6-Jun-12 20:25pm
Comments
Alf1har at 7-Jun-12 3:37am
   
Hi Lakhwant

Is it possible for you to put a trace on SQL to see what exactly is being passed to SQL to execute, and check whether the statement is actually returning values if you execute it with the same parameters by hand?

Thank you

Ragards

Alf
db7uk at 7-Jun-12 4:16am
   
does your complex type mirror the data types returned from the query?

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 524
1 OriginalGriff 245
2 Peter Leow 194
3 Abhinav S 102
4 Suvendu Shekhar Giri 95
0 OriginalGriff 9,428
1 Sergey Alexandrovich Kryukov 9,037
2 CPallini 5,189
3 Maciej Los 4,991
4 Mika Wendelius 3,856


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 7 Jun 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100