SQL Procedure Get List with datatable

USE [Nobolo]

GO

/****** Object:  StoredProcedure [dbo].[AspGetListOfSocialPhotos]    Script Date: 7/8/2020 1:19:29 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[AspGetListOfSocialPhotos]  --'','asc','Email',1,10,null,null

@Search as nvarchar(200) = '',

@SortBy as nvarchar(10) = '',

@SortColumn as nvarchar(max) = '',

@PageIndex as int = 1,

@PageSize as int = 10,

@StartDate as datetime = '',

@EndDate as datetime = '',

@RecordStatus as nvarchar(200) = 'Active',

@ReturnMessage varchar(200) = null output ,

@ReturnValue bigint = 0

AS

BEGIN

Declare @StartPage as int = 0;

Declare @EndPage as int = 0;

set @StartPage = (@PageIndex)

set @EndPage = (@PageIndex) + (@PageSize-1);

Select * from (Select 

ROW_NUMBER() OVER 

(

ORDER BY 

CASE WHEN (@SortColumn = 'CreatedDate' AND @SortBy = 'ASC') THEN A.CreatedDate END ASC,

CASE WHEN (@SortColumn = 'CreatedDate' AND @SortBy = 'DESC') THEN A.CreatedDate END DESC,

CASE WHEN (@SortColumn = 'ModifiedDate' AND @SortBy = 'ASC') THEN A.ModifiedDate END ASC,

CASE WHEN (@SortColumn = 'ModifiedDate' AND @SortBy = 'DESC') THEN A.ModifiedDate END DESC,

CASE WHEN (@SortColumn = 'MediumImageURL' AND @SortBy = 'ASC') THEN A.MediumImageURL END ASC,

CASE WHEN (@SortColumn = 'MediumImageURL' AND @SortBy = 'DESC') THEN A.MediumImageURL END DESC,

CASE WHEN (@SortColumn = 'UploadedPhotosID' AND @SortBy = 'ASC') THEN A.UploadedPhotosID END ASC,

CASE WHEN (@SortColumn = 'UploadedPhotosID' AND @SortBy = 'DESC') THEN A.UploadedPhotosID END DESC

) AS RowIndex,

CAST(@PageIndex AS VARCHAR)   as PageNo,

COUNT(A.UploadedPhotosID) OVER (PARTITION BY -1) AS T_Rec,

CEILING(CAST(COUNT(A.UploadedPhotosID) OVER (PARTITION BY -1) AS DECIMAL) /  CAST(CAST(@PageSize  AS VARCHAR) AS DECIMAL) ) AS T_Pages,

* from (

Select

ISNULL(B.UploadedPhotosID,'') as UploadedPhotosID,

ISNULL(B.MediumImageURL,'') as MediumImageURL,

ISNULL(B.RecordStatus,'') as RecordStatus,

ISNULL([dbo].[GetESTfromUTC] (B.CreatedDate),'') as CreatedDate,

ISNULL([dbo].[GetESTfromUTC] (B.ModifiedDate),'') as ModifiedDate,

ISNULL(C.[Email],'') as [Email],

ISNULL(B.[IsBlockedByAdmin],'') as IsBlockedByAdmin

from [dbo].[tblUploadedPhotos] B

Left Join [dbo].[tblUsers] C

ON C.UsersId = B.UserId

where

--B.RecordStatus=@RecordStatus

--and B.isPublic=1

B.RecordStatus='Active'

AND

B.isPublic = Case When @RecordStatus='Active'

THEN

1

ELSE

  0

END

and (B.isPurchase=1)

AND

IsNULL(B.IsBlockedByAdmin,0) = Case When @RecordStatus='Active'

THEN

0

ELSE

  1

END

and (B.isPurchase=1)

--AND B.Name is not null

     AND  

(

(@Search is not null and @Search != '' 

and  (

(B.MediumImageURL like '%' + @Search + '%')

or (B.RecordStatus like '%' + @Search + '%')

)

) 

OR (isnull(@Search,'') = '')

)

 

AND 

  

(

((@StartDate is not null and @StartDate != '' 

and ISNULL([dbo].[GetESTfromUTC] (B.CreatedDate),'') >= @StartDate) or (ISNULL(@StartDate,'') = ''))

AND

((@EndDate is not null and @EndDate != '' 

and ISNULL([dbo].[GetESTfromUTC] (B.CreatedDate),'') <= @EndDate) or (ISNULL(@EndDate,'') = ''))

)

 

) A 

) A where A.RowIndex >= @StartPage And A.RowIndex <= @EndPage

END