SqlServer2000问题

--1.有，关键字为：identity
--2.一般用存储过程

1、id设置为标识列
2、分页参考分页存储过程收藏
ALTER  PROCEDURE [dbo].[book_fenye]
@tblName varchar(255), -- 表名
@mainid varchar(255), --主键字段名
@strGetFields varchar(1000) = '', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '', -- 查询条件 (注意不要加 where)
@PageSize int = 60, -- 页尺寸
@PageIndex int = 1 -- 页码 --WITH ENCRYPTION 打开选项将对存储过程进行加密，慎重，加密后无法恢复
AS
SET NOCOUNT ON declare @strSQL varchar(5000) -- 主语句
declare @strOrder1 varchar(400) -- 排序类型
declare @strOrder2 varchar(400) -- 排序类型
declare @tmp_filed varchar(400) -- 排序类型  if @fldName@mainid
  set @tmp_filed=' '+@mainid+','+@fldName
else
  set @tmp_filed=' '+@fldName

    if @OrderType  0 --如果@OrderType不是0，就执行降序，这句很重要！
  begin
   set @strOrder1 = ' order by ' + @fldName +' desc'
   set @strOrder2 = ' order by ' + @fldName
     end    else
  begin
   set @strOrder1 = ' order by ' + @fldName
   set @strOrder2 = ' order by ' + @fldName +' desc'
     end
    if @PageIndex = 1
        begin
            if @strWhere  ''
                set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' '+@strGetFields+ ' from ' + @tblName + ' with(nolock) where ' + @strWhere + @strOrder1
            else
                set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' '+@strGetFields+ ' from '+ @tblName + ' with(nolock)  '+ @strOrder1
                --如果是第一页就执行以上代码，这样会加快执行速度
        end
    else
        begin
            --以下代码赋予了@strSQL以真正执行的SQL代码
            if @strWhere = ''
    set @strsql='select '+ @strGetFields + ' from ' + @tblName +' with(nolock) where '+@mainid+' in'
     +'(select top '+ ltrim(str(@PageSize)) + ' '+@mainid+' from '
     +'(select top '+ ltrim(str(@PageIndex@PageSize))+@tmp_filed+' from '
     +@tblName+' with(nolock) '+@strOrder1+') as aaa '+@strOrder2+')'
     +@strOrder1   else
    set @strsql='select '+ @strGetFields + ' from ' + @tblName +' with(nolock) where '+@mainid+' in'
     +'(select top '+ ltrim(str(@PageSize)) + ' '+@mainid+' from '
     +'(select top '+ ltrim(str(@PageIndex@PageSize))+@tmp_filed+' from '
     +@tblName+' with(nolock) where '+@strWhere+@strOrder1+') as aaa '+@strOrder2+')'
     +@strOrder1  end  exec (@strSQL) --select @strSQL as aaa

分页存储过程参考：http://blog.csdn.net/zjcxc/archive/2003/12/29/20080.aspx

分页参考2
针对数据库数据在UI界面上的分页是老生常谈的问题了，网上很容易找到各种“通用存储过程”代码，而且有些还定制查询条件，看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQL SERVER 2000的分页存储过程，同时谈谈SQL SERVER 2005下分页存储过程的演进。在进行基于UI显示的数据分页时，常见的数据提取方式主要有两种。第一种是从数据库提取所有数据然后在系统应用程序层进行数据分页，显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。
以下是笔者对两种实现方式所做的优缺点比较，针对应用程序编写，笔者以.NET技术平台为例。
类别 SQL语句代码编写设计时性能
第一种语句简单，兼容性好很少完全支持数据越大性能越差
第二种看具体情况较多部分支持良好，跟SQL语句有关
对于第一种情况本文不打算举例，第二种实现方式笔者只以两次TOP方式来进行讨论。
在编写具体SQL语句之前，定义以下数据表。
数据表名称为：Production.Product。Production为SQL SERVER 2005中改进后的数据表架构，对举例不造成影响。
包含的字段为：
列名数据类型允许空说明
ProductID Int   产品ID，PK。
Name Nvarchar(50)   产品名称。不难发现以上表结构来自SQL SERVER 2005 样例数据库AdventureWorks的Production.Product表，并且只取其中两个字段。
分页相关元素：
PageIndex – 页面索引计数，计数0为第一页。
PageSize – 每个页面显示大小。
RecordCount – 总记录数。
PageCount – 页数。对于后两个参数，笔者在存储过程中以输出参数提供。

1．SQL SERVER 2000中的TOP分页
CREATE PROCEDURE [Zhzuo_GetItemsPage]
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize  INT, /*页面大小*/
    @RecordCount INT OUT, /*总记录数*/
    @PageCount INT OUT /*页数*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex DECLARE @SQLSTR NVARCHAR(1000) IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
    'ProductID,Name FROM Production.Product ORDER BY ProductID DESC'
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
       'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
    END
    ELSE
    BEGIN
       SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
       'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
    END
END
/*执行*/
EXEC (@SQLSTR)
以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用2次TOP翻转。其中排序条件为ProductID倒序。最后通过EXECUTE执行SQL字符串拼串。 2．SQL SERVER 2005中的TOP分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
    @PageIndex INT,
    @PageSize  INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex /*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SELECT TOP(@PageSize) ProductID,Name FROM Production.Product ORDER BY ProductID DESC
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
       ORDER BY ProductID DESC
    END
    ELSE
    BEGIN
       SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
       ORDER BY ProductID DESC
    END
END
以上存储过程是使用2005的TOP (表达式) 新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。 3．SQL SERVER 2005中的新分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
    @PageIndex INT,
    @PageSize  INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProductID,Name FROM
(SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize)  and T.SerialNumber <= ((@PageIndex+1) * @PageSize)

第三个存储过程使用2005下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的ProductID为主键,根据ProductID进行排序生成ROW_NUMBER，通过ROW_NUMBER来确定具体的页数。通过对三个分页存储过程的比较，可见SQL SERVER 的TSQL 语言对分页功能的支持进步不少。使分页实现趋向于简单化。

存储过程好复杂还是top爽

调试易

SqlServer2000问题

解决方案 »