EmailMail Query

GO
/****** Object: StoredProcedure [dbo].[Mail]
10:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Usp_MailHeadersGetAll]
@MessageType Char(3)='INB'
,@User_FK BIGINT=0
,@Email_FK BIGINT=0
,@datefrom varchar(15)=''
,@dateto varchar(15)=''
,@PageIndex int=1
,@RowsPerPage int=99999
,@HasAttachments Bit=0
,@IsRead bit=0
,@IsUnread bit =0
,@OrderBY smallint=4
,@Order smallint=2

as
BEGIN

Declare @strpaging as nvarchar(4000)
Declare @strqry as nvarchar(4000)

if(len(@MessageType)=3)
BEGIN

--PagingData
set @strpaging = 'SELECT count(header_pk) as ''TotalRecords'''
set @strpaging = @strpaging + ' FROM crm_mail_headers (nolock) INNER JOIN'
set @strpaging = @strpaging + ' crm_Mail_Action (nolock) ON crm_mail_headers.action_fk = crm_Mail_Action.action_pk '
set @strpaging = @strpaging + ' where crm_mail_headers.User_FK ='+ CAST(@User_FK as varchar) +' and crm_mail_headers.Email_FK ='+ CAST(@Email_FK as varchar)
if(@HasAttachments=1)
set @strpaging = @strpaging + ' and crm_mail_headers.HasAttachment =1 '
if(@IsRead=1)
set @strpaging = @strpaging + ' and crm_mail_headers.IsRead =1 '
if(@IsUnread=1)
set @strpaging = @strpaging + ' and crm_mail_headers.IsRead =0 '

if(@MessageType='INB')
set @strpaging = @strpaging + ' and crm_mail_headers.IsInbox =1 '
if(@MessageType='SNT')
set @strpaging = @strpaging + ' and crm_mail_headers.IsSent =1 '
if(@MessageType='DRA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsDraft =1 '
if(@MessageType='SPA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsSpam =1 '
if(@MessageType='TRA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsDeleted =1 '

--ListData

set @strqry = 'SELECT * FROM '
set @strqry = @strqry + ' ( '
set @strqry = @strqry + ' SELECT ROW_NUMBER() OVER(ORDER BY header_pk desc) as RowNum ,header_pk,user_Fk,UniqueId,ReceivedDate,MessageId,HasAttachment,IsBodySaved,IsRead,ActionComment,'
set @strqry = @strqry + ' MessageFrom As Sender,Subject as Sub,action_fk,'
set @strqry = @strqry + ' Case IsRead When 0 then ''Images/new.jpg'' else ''Images/old.gif'' ENd as ''EMailStatus'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+MessageFrom+'''' else MessageFrom END as ''MessageFrom'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+isnull(MessageTo,'''')+'''' else isnull(MessageTo,'''') END as ''MessageTo'','
set @strqry = @strqry + ' Case HasAttachment When 1 then '''' else ''   '' ENd as ''AttchmentStatus'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+Subject+'''' else Subject END as ''Subject'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+Action+'''' else Action END as ''Action'''

set @strqry = @strqry + ' FROM crm_mail_headers (nolock) INNER JOIN'
set @strqry = @strqry + ' crm_Mail_Action (nolock) ON crm_mail_headers.action_fk = crm_Mail_Action.action_pk '
set @strqry = @strqry + ' where crm_mail_headers.User_FK ='+ CAST(@User_FK as varchar) +' and crm_mail_headers.Email_FK ='+ CAST(@Email_FK as varchar)
if(@HasAttachments=1)
set @strqry = @strqry + ' and crm_mail_headers.HasAttachment =1 '
if(@IsRead=1)
set @strqry = @strqry + ' and crm_mail_headers.IsRead =1 '
if(@IsUnread=1)
set @strqry = @strqry + ' and crm_mail_headers.IsRead =0 '

if(@MessageType='INB')
set @strqry = @strqry + ' and crm_mail_headers.IsInbox =1 '
if(@MessageType='SNT')
set @strqry = @strqry + ' and crm_mail_headers.IsSent =1 '
if(@MessageType='DRA')
set @strqry = @strqry + ' and crm_mail_headers.IsDraft =1 '
if(@MessageType='SPA')
set @strqry = @strqry + ' and crm_mail_headers.IsSpam =1 '
if(@MessageType='TRA')
set @strqry = @strqry + ' and crm_mail_headers.IsDeleted =1 '

set @strqry = @strqry + ' ) as Mails'

set @strqry = @strqry + ' WHERE RowNum BETWEEN '+ cast(@PageIndex as varchar(100)) +' AND '+ cast( ((@PageIndex + @RowsPerPage) - 1) as varchar(100))
--Order By [Field]
if(@OrderBY=1)
set @strqry = @strqry + ' Order By ReceivedDate '
else if(@OrderBY=2)
set @strqry = @strqry + ' Order By Sender '
else if(@OrderBY=3)
set @strqry = @strqry + ' Order By Sub '
else if(@OrderBY=4)
set @strqry = @strqry + ' Order By ReceivedDate '
else if(@OrderBY=5)
set @strqry = @strqry + ' Order By Action '
else
set @strqry = @strqry + ' Order By header_pk '

--- OrderBy

if(@Order=1)
set @strqry = @strqry + ' ASC'
if(@Order=2)
set @strqry = @strqry + ' DESC'

END

EXECUTE sp_executesql @strpaging
EXECUTE sp_executesql @strqry
-- select @strqry

END