代码如下复制代码,听说效率比较高的一个存储过程

  • 栏目:数据 时间:2020-04-14 23:02
<返回列表

文章有二个实例一个是sql2000的分页存储过程一个是sql2005的分页存储过程,有需要的同学可以拿去看看。 代码如下复制代码 CREATE PROCEDURE [dbo].[ProcCustomPage] ( @Table_Name varchar(5000), --表名 @Sign_Record varchar(50), --主键 @Filter_Condition varchar(1000), --筛选条件,不带where @Page_Size int, --页大小 @Page_Index int, --页索引 @TaxisField varchar(1000), --排序字段 @Taxis_Sign int, --排序方式 1为 DESC, 0为 ASC@Find_RecordList varchar(1000), --查找的字段 @Record_Count int --总记录数 ) AS BEGIN DECLARE @Start_Number int DECLARE @End_Number int DECLARE @TopN_Number int DECLARE @sSQL varchar(8000)if(@Find_RecordList='')BEGINSELECT @Find_RecordList='*'END SELECT @Start_Number =(@Page_Index-1) * @Page_Size IF @Start_Number=0 SElECT @Start_Number=0 SELECT @End_Number=@Start_Number+@Page_Size IF @End_Number@Record_Count SELECT @End_Number=@Record_Count SELECT @TopN_Number=@End_Number-@Start_Number IF @TopN_Number=0 SELECT @TopN_Number=0 print @TopN_Number print @Start_Number print @End_Number print @Record_CountIF @TaxisField=''beginselect @TaxisField=@Sign_Recordend IF @Taxis_Sign=0 BEGIN IF @Filter_Condition='' BEGIN SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField END ELSE BEGIN SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField END END ELSE BEGIN IF @Filter_Condition='' BEGIN SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC' END ELSE BEGIN SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC' END END EXEC (@sSQL) IF @@ERROR0 RETURN -3 RETURN 0 END PRINT @sSQLGO

文章找到了两篇关于mssql server存储过程的高效分页代码,有需要的朋友可以参考一下。 代码如下复制代码

文章也是从朋友那里抄来的两个不错的sql分页存储过程实现,听说效率比较高的一个存储过程,有需要的同学可以了解一下或提供更好的解决方案。

sql2005

Create PROC P_viewPage /**//* nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284 敬告:适用于单一主键或存在唯一值列的表或视图 ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 */ @TableName VARCHAR(200), --表名 @FieldList VARCHAR(2000), --显示列名,如果是全部字段则为* @PrimaryKey VARCHAR(100), --单一主键或唯一值键 @Where VARCHAR(2000), --查询条件 不含'where'字符,如id10 and len(userid)9 @Order VARCHAR(1000), --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc --注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @TotalCount INT OUTPUT , --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数 AS SET NOCOUNT ON IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0 SET @Order = RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','') WHILE CHARINDEX(', ',@Order) 0 or CHARINDEX(' ,',@Order) 0 BEGIN SET @Order = REPLACE(@Order,', ',',') SET @Order = REPLACE(@Order,' ,',',') END IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = '' or ISNULL(@PrimaryKey,'') = '' or @SortType 1 or @SortType 3 or @RecorderCount 0 or @PageSize 0 or @PageIndex 0 BEGIN PRINT('ERR_00') RETURN END IF @SortType = 3 BEGIN IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC') BEGIN PRINT('ERR_02') RETURN END END DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000) DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000) DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE @SqlCount NVARCHAR(4000) IF ISNULL(@where,'') = '' BEGIN SET @new_where1 = ' ' SET @new_where2 = ' Where ' END ELSE BEGIN SET @new_where1 = ' Where ' + @where SET @new_where2 = ' Where ' + @where

这个还觉得不错的一个存储过程

代码如下复制代码

代码如下复制代码

CREATE PROCEDURE [dbo].[GetRecordFromPage] @SelectList VARCHAR(2000), --欲选择字段列表@TableSource VARCHAR(100), --表名或视图表 @SearchCondition VARCHAR(2000), --查询条件@OrderExpression VARCHAR(1000), --排序表达式@PageIndex INT = 1, --页号,从0开始@PageSize INT = 10 --页尺寸AS BEGINIF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''BEGINSET @SelectList = '*'ENDPRINT @SelectList

自己写的一个

/****** 对象: StoredProcedure [dbo].[P_viewPage] 脚本日期: 05/14/2012 08:49:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

SET @SearchCondition = ISNULL(@SearchCondition,'')SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))IF @SearchCondition ''BEGINIF UPPER(SUBSTRING(@SearchCondition,1,5)) 'WHERE'BEGINSET @SearchCondition = 'WHERE ' + @SearchConditionENDENDPRINT @SearchCondition

代码如下复制代码

create PROC [dbo].[P_viewPage]-- Add the parameters for the stored procedure here@TableName VARCHAR(200), --表名@FieldList VARCHAR(2000), --显示列名,如果是全部字段则为*@PrimaryKey VARCHAR(100), --单一主键或唯一值键@Where VARCHAR(8000), --查询条件不含'where'字符,如id10 and len(userid)9@Order VARCHAR(1000), --排序不含'order by'字符,如id asc,userid desc,必须指定asc或desc--注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷@SortType INT, --排序规则1:正序asc 2:倒序desc 3:多列排序方法@RecorderCount INT, --记录总数0:会返回总记录@PageSize INT, --每页输出的记录数@PageIndex INT, --当前页数@TotalCount INT OUTPUT, --记返回总记录@TotalPageCount INT OUTPUT --返回总页数AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ONIF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0SET @Order = RTRIM(LTRIM(@Order))SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')WHILE CHARINDEX(', ',@Order) 0 OR CHARINDEX(' ,',@Order) 0BEGINSET @Order = REPLACE(@Order,', ',',')SET @Order = REPLACE(@Order,' ,',',')ENDIF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''OR ISNULL(@PrimaryKey,'') = ''OR @SortType 1 OR @SortType 3OR @RecorderCount 0 OR @PageSize 0 OR @PageIndex 0BEGIN PRINT('ERR_00参数错误') RETURNENDIF @SortType = 3BEGINIF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')BEGIN PRINT('ERR_02排序错误') RETURN ENDENDDECLARE @new_where1 VARCHAR(8000)DECLARE @new_where2 VARCHAR(8000)DECLARE @new_order1 VARCHAR(1000)DECLARE @new_order2 VARCHAR(1000)DECLARE @new_order3 VARCHAR(1000)DECLARE @Sql VARCHAR(8000)DECLARE @SqlCount NVARCHAR(4000)IF ISNULL(@where,'') = ''BEGINSET @new_where1 = ' 'SET @new_where2 = ' WHERE 'ENDELSEBEGINSET @new_where1 = ' WHERE ' + @whereSET @new_where2 = ' WHERE ' + @where + ' AND 'ENDIF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2BEGINIF @SortType = 1 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC' ENDIF @SortType = 2 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC' ENDENDELSEBEGINSET @new_order1 = ' ORDER BY ' + @OrderENDIF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)0BEGINSET @new_order1 = ' ORDER BY ' + @OrderSET @new_order2 = @Order + ','SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)IF @FieldList '*' BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') SET @FieldList = ',' + @FieldList WHILE CHARINDEX(',',@new_order3)0 BEGIN IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')0 BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) END SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3)) END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) ENDENDSET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1IF @RecorderCount = 0BEGINEXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',@TotalCount OUTPUT,@TotalPageCount OUTPUTENDELSEBEGINSELECT @TotalCount = @RecorderCountENDIF @PageIndex CEILING((@TotalCount+0.0)/@PageSize)BEGINSET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)ENDIF @PageIndex = 1 OR @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)BEGINIF @PageIndex = 1 --返回第一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 ENDIF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList

SET @OrderExpression = ISNULL(@OrderExpression,'')SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))IF @OrderExpression ''BEGINIF UPPER(SUBSTRING(@OrderExpression,1,5)) 'WHERE'BEGINSET @OrderExpression = 'ORDER BY ' + @OrderExpressionENDENDPRINT @OrderExpression

USE [CaiLi]GO

IF @PageIndex IS NULL OR @PageIndex 1BEGINSET @PageIndex = 1ENDPRINT @PageIndexIF @PageSize IS NULL OR @PageSize 1BEGINSET @PageSize = 10ENDPRINT @PageSize

/****** Object: StoredProcedure [dbo].[SqlPagination] Script Date: 10/26/2011 11:40:46 ******/SET ANSI_NULLS ONGO

--输出内容SELECT @TotalCount as N'@TotalCount', @TotalPageCount as N'@TotalPageCount'

DECLARE @SqlQuery VARCHAR(4000)

SET QUOTED_IDENTIFIER ONGO

再分享一个,一个比较创新的存储过程:(注:此存储过程利用了sql2005函数,适用于sql20005极其以上版本)

SET @SqlQuery='SELECT '+@SelectList+',RowNumber FROM (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) + ' AND ' + CAST((@PageIndex * @PageSize) AS VARCHAR) -- ORDER BY ' + @OrderExpressionPRINT @SqlQuerySET NOCOUNT ONEXECUTE(@SqlQuery)SET NOCOUNT OFF

CREATE PROCEDURE [dbo].[SqlPagination]

代码如下复制代码

RETURN @@RowCountEND

/*

/****** 对象: StoredProcedure [dbo].[QueryPagination] 脚本日期: 05/14/2012 08:57:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

***************************************************************

/*-------------------------------------------------* strFieldList 字段列表* strTableList 查询表列表* strWhereClause 查询条件* strOrderFld 排序字段* intTotoRecords (输入/输出)总记录数* intTotoPages (输入/输出)总页数* intPageSize 每页记录数* intCurrentPage 当前页* intCountToto 是否统计总数* 0: 不容积总数* 1: 统计总数-------------------------------------------------*/CREATE PROCEDURE [dbo].[QueryPagination] @t char(1)='', @strFieldList varchar(1000)='', @strTableList varchar(300)='', @strWhereClause varchar(1000)='', @strOrderFld varchar(100)='', @intCurrentPage int=1, @intPageSize int=10, @intCountToto int=0, @intTotoRecords int=0 out, @intTotoPages int=0 outAS

** 千万数量级分页存储过程 **

BEGIN

***************************************************************

DECLARE @strSQL nvarchar(4000) DECLARE @intCurPage int DECLARE @strWhere varchar(200) DECLARE @setvalue_error int

参数说明:

set @strWhere = ''; -- Where 语句

1.Tables :表名称,视图

If @strWhereClause '' set @strWhere = @strWhereClause;

2.PrimaryKey :主关键字

Set xact_abort onBegin Tran

3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc

SET @strSQL = 'SELECT COUNT(1) FROM ' + @strTableList + ' ' + @strWhere;

4.CurrentPage :当前页码

exec('DECLARE cur_t CURSOR FOR '+ @strSQL)

5.PageSize :分页尺寸

OPEN cur_t FETCH NEXT FROM cur_t into @intTotoRecords while @@fetch_status = 0 begin fetch next from cur_t into @intTotoRecords end close cur_t deallocate cur_t

6.Filter :过滤语句,不带Where

Set @setvalue_error = @@errorIf @setvalue_error0 Begin Set @intTotoRecords = -1; GOTO DoNext; Rollback Tran End Else Begin Commit Tran GOTO DoNext; End

7.Group :Group语句,不带Group By

DoNext: IF @intTotoRecords0 GOTO errTotoRecords; -- 返回错误:记录总数错误

***************************************************************/

IF @intPageSize=0 GOTO errPageSize; -- 返回错误:每页记录数范围错误

(

-- 计算出总页数 IF @intTotoRecords%@intPageSize 0 set @intTotoPages = cast(@intTotoRecords/@intPageSize as int) + 1; ELSE set @intTotoPages=cast(@intTotoRecords/@intPageSize as int) ;

@Tables varchar(1000),

-- 确定待查询的页码数 -- 如果页码数小于等于 0 ,则查询返回第一页 -- 如果页码数大于最大页码数,则查询返回最后一页 IF @intCurrentPage=0 set @intCurPage=1; ELSE IF @intCurrentPage@intTotoPages set @intCurPage = @intTotoPages; ELSE set @intCurPage=@intCurrentPage;

@PrimaryKey varchar(100),

If @strWhere '' Set @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @strOrderFld + ') ROWNUM,' + @strFieldList + ' FROM ' + @strTableList + ' ' + @strWhere + ') TAB_TMP WHERE ROWNUM' + cast((@intCurPage-1)*@intPageSize as varchar) + ' and ROWNUM ' + cast(@intCurPage*@intPageSize+1 as varchar); Else Set @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @strOrderFld + ') ROWNUM,' + @strFieldList + ' FROM ' + @strTableList + ') TAB_TMP WHERE ROWNUM' + cast((@intCurPage-1)*@intPageSize as varchar) + ' and ROWNUM ' + cast(@intCurPage*@intPageSize+1 as varchar) ; Execute(@strSQL);

@Sort varchar(200) = NULL,

If @@error0 return @@error; Else return 0;

@CurrentPage int = 1,

errPageSize: return -1;

@PageSize int = 10,

errTotoRecords: return -2;

@Fields varchar(1000) = '*',

END

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL)

AS

/*默认排序*/if @PrimaryKey IS NULL or @PrimaryKey = ''set @PrimaryKey='ID'

IF @Sort IS NULL or @Sort = ''

SET @Sort = @PrimaryKey

IF @Fields IS NULL or @Fields = ''

SET @Fields = '*'

DECLARE @SortTable varchar(100)

DECLARE @SortName varchar(100)

DECLARE @strSortColumn varchar(200)

DECLARE @operator char(2)

DECLARE @type varchar(100)

DECLARE @prec int

/*设定排序语句.*/if charindex(',',@Sort) 0 set @strSortColumn = substring(@Sort,0,charindex(',',@Sort)) elseset @strSortColumn = @SortIF CHARINDEX('DESC',@Sort)0

BEGIN

SET @strSortColumn = REPLACE(@strSortColumn, 'DESC', '')

SET @operator = '='

END

ELSE

BEGIN

IF CHARINDEX('ASC',@Sort) 0BEGINSET @strSortColumn = REPLACE(@strSortColumn, 'ASC', '')

SET @operator = '='ENDEND

IF CHARINDEX('.', @strSortColumn) 0

BEGIN

SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@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) + ')'

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50)

DECLARE @strFilter varchar(1000)

DECLARE @strSimpleFilter varchar(1000)

DECLARE @strGroup varchar(1000)

DECLARE @strSort varchar(200)

/*默认当前页*/

IF @CurrentPage 1

SET @CurrentPage = 1

/*设置分页参数.*/

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 1=1 ' + @Filter + ' '

SET @strSimpleFilter =@Filter + ' '

END

ELSE

BEGIN

SET @strSimpleFilter = ''

SET @strFilter = ''

END

IF @Group IS NOT NULL AND @Group != ''

SET @strGroup = ' GROUP BY ' + @Group + ' '

ELSE

SET @strGroup = ''

IF @Sort IS NOT NULL AND @Sort != ''

SET @strSort = ' ORDER BY ' + @Sort + ' '

ELSE

SET @strSort = ''

--print('Select ' + @Fields + ' FROM ' + '(Select *,ROW_NUMBER() OVER ('+@strSort+')as RowNumber FROM '+@Tables+') t' + ' Where t.RowNumber between '+@strStartRow+' and '+' ' + @strSimpleFilter + ' ' + @strSort + @strGroup)/*执行查询语句*/declare @STRORDER varchar(50)if CHARINDEX(',',@strSort)0set @STRORDER=SUBSTRING(@strSort, 0, CHARINDEX(',',@strSort))elseset @STRORDER=@strSortEXEC(

' DECLARE @SortColumn ' + @type + 'DECLARE @TotalCount intDECLARE @ENDCOUNT intDECLARE @strENDCOUNT varchar(50)

--Select count(1) FROM ' + @Tables + @strFilter+'

set @TotalCount=(Select count(1) FROM ' + @Tables + @strFilter+')'+'SET ROWCOUNT ' + @strStartRow + 'SET @ENDCOUNT=CAST('+@strStartRow+' AS int)+CAST('+@strPageSize+' AS int)-1

IF @ENDCOUNT @TotalCountBEGINSET @ENDCOUNT = @TotalCountENDset @strENDCOUNT=CAST(@ENDCOUNT AS varchar(50))

Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter

SET ROWCOUNT ' + @strPageSize + '

Select ' + @Fields + ' FROM ' + '(Select *,ROW_NUMBER() OVER ('+@STRORDER+')as RowNumber FROM '+@Tables+' where 1=1 '+@strSimpleFilter+') t' + ' Where t.RowNumber between '+@strStartRow+' and @strENDCOUNT ' + @strGroup + @strSort + ' ')GO

上一篇:使用带关联子查询的Update更新 --1.创建测试表,INSERT INTO SELECT语句复制表数据 下一篇:良好的规则是只选所需

更多阅读

代码如下复制代码,听说效率比较高的一

数据 2020-04-14
文章有二个实例一个是sql2000的分页存储过程一个是sql2005的分页存储过程,有需要的同学可以拿...
查看全文

数据库名.dbo.表名),因为此组件已作为此

数据 2020-04-14
数据库教程1:AAA数据库2:BBB 如果出现错误提示: SQL Server 阻止了对组件 'Ad Hoc DistributedQuer...
查看全文

韦德体育9pt备份名称和位置/span

数据 2020-04-14
前台代码: %@ Page Language=C# AutoEventWireup=trueCodeBehind=SqlDbMgmt.asp教程x.cs Inherits=SysSourceMgmt.SqlDbMg...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有