SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectMercSendMail]
(
@IsApproved varchar(10),
@IsLockedOut varchar(10),
@UserName nvarchar(256),
@Email nvarchar(256),
@CreateDateTimeFrom datetime,
@CreateDateTimeTo datetime,
@iCurrentPage int,
@iPageSize int,
@vchrSortField VARCHAR(50),
@vchrSortType VARCHAR(50),
@iRecordCount INT OUTPUT
)
AS
BEGIN
DECLARE @SQLSTRING NVARCHAR(MAX)
DeCLARE @SQLWHERE NVARCHAR(MAX)
DeCLARE @SQLWHEREUSERNAME NVARCHAR(256)
DECLARE @iMinPage INT
DECLARE @iMaxPage INT
SET @iMinPage=(@iCurrentPage-1)*@iPageSize+1
SET @iMaxPage=@iMinPage+@iPageSize-1
SET @SQLWHERE=' Where 1=1 '
IF ISNULL(@IsApproved,'')<>''
BEGIN
SET @SQLWHERE=@SQLWHERE+' AND IsApproved='''+@IsApproved+''''
END
IF ISNULL(@IsLockedOut,'')<>''
BEGIN
SET @SQLWHERE=@SQLWHERE+' AND IsLockedOut='''+@IsLockedOut+''''
END
IF ISNULL(@Email,'')<>''
BEGIN
SET @SQLWHERE=@SQLWHERE+' AND Email LIKE ''%'+@Email+'%'''
END
IF ISNULL(@CreateDateTimeFrom,'')<>''
BEGIN
SET @SQLWHERE=@SQLWHERE+' AND CreateDate>='+''''+CAST(@CreateDateTimeFrom as varchar(100))+''''
IF ISNULL(@CreateDateTimeTo,'')<>''
BEGIN
SET @SQLWHERE=@SQLWHERE+' AND CreateDate<='+''''+CAST(@CreateDateTimeTo as varchar(100))+''''
END
END
SET @SQLWHEREUSERNAME=' WHERE 1=1 '
IF ISNULL(@UserName,'')<>''
BEGIN
SET @SQLWHEREUSERNAME=@SQLWHEREUSERNAME+' AND UserName LIKE ''%'+@UserName+'%'''
END
DECLARE @SQLSTRING1 AS NVARCHAR(MAX)
DECLARE @PARAMString NVARCHAR(MAX)
SET @SQLSTRING1='SELECT @TotalCount=COUNT(*) FROM [8SMembership].dbo.aspnet_Membership AS A
'+' Join ('+'SELECT UserName,UserID FROM [8SMembership].dbo.aspnet_Users'
+@SQLWHEREUSERNAME+')'+' AS C on A.UserID=C.UserID'+@SQLWHERE
SET @PARAMString='@TotalCount int output'
SET @SQLSTRING='SELECT * FROM ('
+'SELECT A.UserId,A.Email,C.UserName,ROW_NUMBER() OVER(ORDER BY '+@vchrSortField+' '+@vchrSortType+') AS RowNumber '
+' FROM [8SMembership].dbo.aspnet_Membership AS A'
+' Join ('+'SELECT UserName,UserID FROM [8SMembership].dbo.aspnet_Users'
+@SQLWHEREUSERNAME+')'+' AS C on A.UserID=C.UserID'+@SQLWHERE
+') AS B
WHERE RowNumber BETWEEN '+CAST(@iMinPage AS VARCHAR(100))+' AND '+CAST(@iMaxPage AS VARCHAR(100))
SET @SQLSTRING=@SQLSTRING
EXEC sp_executesql @SQLSTRING
print @SQLSTRING
exec sp_executesql @SQLSTRING1,@PARAMString,@TotalCount=@iRecordCount output
END