ALTER PROCEDURE dbo.procListAllUser
(
@StrGetFields varchar(1000) ="*",
@FieldName varchar(255),
@PageSize int = 10,
@PageIndex int = 1,
@TotalCount bit = 0,
@OrderType bit = 0,
@StrWhere varchar(1500)
)
AS
declare @StrSQL varchar(5000)
declare @StrTmp varchar(110)
declare @StrOrder varchar(400)
IF @TotalCount != 0
BEGIN
IF @StrWhere !=''''
SET @StrSQL = 'select count(*) as Total from [Users] where '+@StrWhere
ELSE
SET @StrSQL = 'select count(*) as Total from [Users]'
END
ELSE
BEGIN
IF @OrderType != 0
BEGIN
SET @StrTmp = '<(select min'
SET @StrOrder = ' order by [' + @FieldName +'] desc'
END
ELSE
BEGIN
SET @StrTmp = '>(select max'
SET @StrOrder = ' order by [' + @FieldName +'] asc'
END
IF @PageIndex = 1
BEGIN
IF @StrWhere != ''''
SET @StrSQL = 'select top ' + str(@PageSize) +' '+@StrGetFields+ '
from [Users] where ' + @StrWhere + ' ' + @StrOrder
ELSE
SET @StrSQL = 'select top ' + str(@PageSize) +' '+@StrGetFields+ '
from [Users] '+ @StrOrder
END
ELSE
BEGIN
SET @strSQL = 'select top' + str(@PageSize) +' '+@StrGetFields+ '
from [Users] where '+ @FieldName + @strTmp + '(['+ @FieldName + '])
from (select top' + str((@PageIndex-1)*@PageSize) + ' ['+ @FieldName + ']
from [Users]' + @StrOrder + ') as tblTmp)'+ @StrOrder
IF @StrWhere != ''''
SET @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [Users] where [' + @FieldName + ']' + @StrTmp + '(['
+ @FieldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @FieldName + '] from [Users] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
END
END
print @StrSQL
exec @StrSQL
RETURN