Click here to Skip to main content
15,910,878 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In code below I am trying to filter by date range fromDate & ToDate but it gives me only 10 data but there are 37 data by fromdate todate , i have also tried to make @DisplayLength optional but it shows nothing for paging sorting searching, range wise Search

What I have tried:

ALTER procedure [dbo].[spGetRetailerDealerServiceCenterList]
(
        @DisplayLength   int           = NULL,
		@DisplayStart    int           = NULL,
		@SortCol         int           = NULL,
		@SortDir         nvarchar(10)  = NULL,
		@Search          nvarchar(255) = NULL,
 	    @FromDate        VARCHAR(20)   = null,
		@ToDate          VARCHAR(20)   = null,
		@Action          char(1)       = null
)
as
begin
        Set @FromDate = ISNULL (@FromDate,'')
		Set @ToDate   = ISNULL (@ToDate,'')

		--if(@FromDate != '' and @ToDate != '')
		--begin		
		--   set @DisplayLength = 100
		--end 
		
		Declare @FirstRec int, @LastRec int,@OffsetValue int,@PagingSize int
		Set @FirstRec = @DisplayStart;
		Set @LastRec = @DisplayStart + @DisplayLength;

		--print   @FromDate
		--print   @ToDate

if(@Action='A')
begin

	
			With CTE_RetailerList as
							(
						Select  Distinct ROW_NUMBER() over (order by
        
								case when (@SortCol = 0 and @SortDir='asc')
								 then DM.DealerId
								end asc,
								case when (@SortCol = 0 and @SortDir='desc')
								 then  DM.DealerId
								end desc,
        
								case when (@SortCol = 1 and @SortDir='asc')
									 then DM.DealerName
								end asc,
								case when (@SortCol = 1 and @SortDir='desc')
									then   DM.DealerName
								end desc,

								case when (@SortCol = 2 and @SortDir='asc')
									then DM.Code
								end asc,
								case when (@SortCol = 2 and @SortDir='desc')
									then DM.Code
								end desc,

								case when (@SortCol = 3 and @SortDir='asc')
									then DM.[Address]
								end asc,
								case when (@SortCol = 3 and @SortDir='desc')
									then DM.[Address]
								end desc,

								case when (@SortCol = 4 and @SortDir='asc')
									then DM.ContactNo
								end asc,
								case when (@SortCol = 4 and @SortDir='desc')
									then DM.ContactNo
								end desc,		

								case when (@SortCol = 5 and @SortDir='asc')
									then DM.EmailId
								end asc,
								case when (@SortCol = 5 and @SortDir='desc')
									then DM.EmailId
								end desc,
		
								case when (@SortCol = 6 and @SortDir='asc')
									then ZM.ZONE_NM
								end asc,
								case when (@SortCol = 6 and @SortDir='desc')
									then ZM.ZONE_NM
								end desc,						
		
								case when (@SortCol = 7 and @SortDir='asc')
									then SM.STATE_NM
								end asc,
								case when (@SortCol = 7 and @SortDir='desc')
									then SM.STATE_NM
								end desc,
		
								case when (@SortCol = 8 and @SortDir='asc')
									then ADM.District_NM
								end asc,
								case when (@SortCol = 8 and @SortDir='desc')
									then ADM.District_NM
								end desc,

								case when (@SortCol = 9 and @SortDir='asc')
									then CM.CITY_NM
								end asc,
								case when (@SortCol = 9 and @SortDir='desc')
									then CM.CITY_NM
								end desc,
								case when (@SortCol = 10 and @SortDir='asc')
									then BM.BRANCH_Name
								end asc,
								case when (@SortCol = 10 and @SortDir='desc')
									then BM.BRANCH_Name
								end desc,

								case when (@SortCol = 11 and @SortDir='asc')
									then DM.CreatedDate
								end asc,
								case when (@SortCol = 11 and @SortDir='desc')
									then DM.CreatedDate
								end desc

								)
								    as RowNum,

								    COUNT(*) over() as TotalCount,
						
									ISNULL	(DM.DealerName,'NA') as DEALERNAME,
									ISNULL	(DM.DealerId,0 )as DEALERID, 			
									ISNULL	(DM.Code,0) as DEALERCODE,				
									 											
									ISNULL	(DM.[Address],'NA') as [ADDRESS],			
									ISNULL	(DM.ContactNo,'NA') as CONTACTNO,			
									ISNULL	(DM.EmailId,'NA')as EMAIL ,				
									ISNULL	(ZM.ZONE_NM,'NA') as ZONENAME,			
									ISNULL	(SM.STATE_NM ,'NA')as STATENAME,			
									ISNULL	(ADM.District_NM,'NA' )as DISTRICTNAME,	
									ISNULL	(CM.CITY_NM,'NA') as CITYNAME,			
									ISNULL	(BM.BRANCH_Name,'NA') as BRANCHNAME,		
								    ISNULL(convert(varchar(20), DM.CreatedDate,103)+' '+convert(varchar(20),convert(time, DM.CreatedDate),100),0)CREATEDDATE,
								    ISNULL(DATENAME(MONTH, DM.CreatedDate),0)Months,
								    ISNULL(CONVERT(VARCHAR(10), CAST( DM.CreatedDate AS TIME), 0),0) times
					
								
									FROM [dbo].[Dealer_Master] DM WITH(NOLOCK)
	
									INNER JOIN	dbo.ZoneMaster ZM WITH(NOLOCK) on ZM.ZONE_ID=DM.ZoneId
									INNER JOIN	[dbo].state_master SM WITH(NOLOCK) on  SM.STATE_ID=DM.StateId
									INNER JOIN	[dbo].[A_DistrictMaster] ADM WITH(NOLOCK) on  ADM.District_ID=DM.DistrictId
									INNER JOIN	[dbo].[A_CITY_MASTER] CM WITH(NOLOCK) on CM.CITY_ID=DM.CityId
									INNER JOIN	[dbo].branch_master BM WITH(NOLOCK) on BM.BRANCH_ID=DM.BranchId											
									
									
									where ((DM.DealerName IS NOT NULL) 

															     
								    AND 
									(
									CONVERT(VARCHAR,DM.CreatedDate,112) >= CONVERT(VARCHAR,CONVERT(DATETIME,@FromDate),112) OR CONVERT(VARCHAR,CONVERT(DATETIME,@FromDate),112) IS NULL 
									AND   CONVERT(VARCHAR,DM.CreatedDate,112) <=  CONVERT(VARCHAR,CONVERT(DATETIME,@ToDate),112)  OR CONVERT(VARCHAR,CONVERT(DATETIME,@ToDate),112)  IS NULL )
									
									AND (  @Search IS NULL
							
										 Or DM.DealerId          like '%' + @Search + '%'
										 Or DM.DealerName		 like '%' + @Search + '%'
										 Or DM.Code				 like '%' + @Search + '%'
										
										 Or DM.[address]         like '%' + @Search + '%'	
										 Or DM.contactno         like '%' + @Search + '%'
										 Or DM.EmailId           like '%' + @Search + '%'
										 Or SM.STATE_NM          like '%' + @Search + '%'
										 Or ADM.District_NM      like '%' + @Search + '%'
										
										 Or CM.CITY_NM           like '%' + @Search + '%'
										 Or BM.BRANCH_Name       like '%' + @Search + '%'
										
										 )	 
							))
							Select *
							from CTE_RetailerList
							where RowNum > @FirstRec and RowNum <= @LastRec	 

							order by  CreatedDate  desc		
end

end
Posted
Updated 16-Feb-21 8:16am
v2
Comments
CHill60 16-Feb-21 11:54am    
It's very difficult to help you without any sample data (for all the tables). You are also more likely to get answers if you simplify your question to only include the relevant code e.g. instead of catering for all those possible sort columns and orders just give us one example that demonstrates the problem.
One issue could be (is likely to be) converting dates to and from varchar - why on earth are you doing that? And don't pass dates into your SP as varchars - use Date type

1 solution

 
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