(
@Tables varchar (1000)--table name such as TestTable
@PrimaryKey varchar,--table's primary key, must be unique
@Sort varchar = NULL,--sort fields such as f_name ASC or F_name desc (Note that you can only have one sort field)
@CurrentPage int = 1,--Current page
@PageSize int =,---per page size
List of fields displayed @Fields varchar (1000) = ' * ',--
@Filter varchar (1000) = NULL,--conditional statement, without where, such as F_id>3
@Group varchar (1000) = NULL,--grouping field
@TotalPage int output--Total number of pages returned
)
With Encryption---encrypted storage
As
SET NOCOUNT on
Declare @intResult Int
Begin Tran
DECLARE @sql nvarchar (4000)
If @Filter is null or @Filter = '
Set @Sql = ' Select @intResult = count (' + @PrimaryKey + ') from ' + @Tables
Else
Set @Sql = ' Select @intResult = count (' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter
EXEC sp_executesql @sql, N ' @intResult int OUTPUT ', @intResult output--total number of records calculated
Select @TotalPage =ceiling ((@intResult +0.0)/@PageSize)--Calculate total pages
IF @Sort is NULL or @Sort = '
SET @Sort = @PrimaryKey
IF CHARINDEX (' DESC ', @Sort) >0
BEGIN
SET @strSortColumn = REPLACE (@Sort, ' DESC ', ')
SET @operator = ' <= '
End
ELSE
IF CHARINDEX (' ASC ', @Sort) > 0
BEGIN
SET @strSortColumn = REPLACE (@Sort, ' ASC ', ')
SET @operator = ' >= '
End
ELSE
BEGIN
SET @strSortColumn = @SORT
SET @operator = ' >= '
End
IF CHARINDEX ('. ', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @strSortColumn))
SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN (@strSortColumn))
End
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
End
Select @type =t.name, @prec =c.prec
From sysobjects o
JOIN syscolumns C on o.id=c.id
JOIN systypes T on C.xusertype=t.xusertype
Where o.name = @SortTable and c.name = @SortName
IF CHARINDEX (' char ', @type) > 0
SET @type = @type + ' (' + CAST (@prec as varchar) + ') '
SET @strPageSize = CAST (@PageSize as varchar (50))
SET @strStartRow = CAST ((@CurrentPage-1) * @PageSize + 1) as varchar (50))
IF @Filter is not NULL and @Filter!= '
BEGIN
SET @strFilter = ' Where ' + @Filter + '
SET @strSimpleFilter = ' and ' + @Filter + '
End
ELSE
BEGIN
SET @strSimpleFilter = '
SET @strFilter = '
End
IF @Group is not NULL and @Group!= '
SET @strGroup = ' GROUP by ' + @Group + '
ELSE
SET @strGroup = '
Set @sql = ' DECLARE @SortColumn ' + @type + '
SET RowCount ' + @strStartRow + '
Select @SortColumn = ' + @strSortColumn + ' from ' + @Tables + @strFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '
SET RowCount ' + @strPageSize + '
Select ' + @Fields + ' from ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '
'
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.