存储过程的创建

在查询窗口输入:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetListByPage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

--高效分页存储过程
--Create by Jinlong Zhang

CREATE PROCEDURE [dbo].[GetListByPage](
@Table varchar(500),          --表名
@Field varchar(500) = ''*'',        --读取字段
@Where  varchar(500) = NULL,       --Where条件
@GroupBy varchar(500) = NULL,      --分组
@OrderBy varchar(500)= NULL,       --排序字段
@PrimaryKeyField varchar(50),      --主键必需 
@PageIndex int = 1,            --开始页码
@PageSize int = 10,             --页大小
@IsCount bit = 0          --返回记录总数

------------------------------------------------------------------------------------------------
--当@IsCount为1时,将同时返回2张表，表0为记录总数，表1为查询结果
------------------------------------------------------------------------------------------------

)
AS
BEGIN
    ------------------------------------------------------------------------------------------------
    DECLARE @strWhere nvarchar(500)                     --Where 条件
    IF @Where IS NOT NULL AND @Where != ''''              --Where 条件
    BEGIN
        SET @strWhere = '' WHERE '' + @Where + '' ''
    END
    ELSE
    BEGIN
        SET @strWhere = ''''
    END
    ----------------------------------------------------------------------------------------------------
    DECLARE @strGroupBy nvarchar(500)                   --GroupBy 条件
    IF @GroupBy IS NOT NULL AND @GroupBy != ''''          --GroupBy 条件
    BEGIN
        SET @strGroupBy = '' GROUP BY '' + @GroupBy + '' ''
    END
    ELSE
    BEGIN
        SET @strGroupBy = ''''
    END
    ----------------------------------------------------------------------------------------------------
    DECLARE @strOrderBy nvarchar(500)                   --OrderBy 条件
    IF @OrderBy IS NULL OR @OrderBy = ''''                --OrderBy 条件
    BEGIN
        SET @strOrderBy = '' ORDER BY '' + @PrimaryKeyField + '' DESC''
    END
    ELSE
    BEGIN
        SET @strOrderBy = '' ORDER BY '' + @OrderBy
    END
    ----------------------------------------------------------------------------------------------------
    DECLARE @strSql nvarchar(max)   --Sql 语句
    --计算总行数
    IF @IsCount = 1
    BEGIN
        SET @strSql= ''SELECT  Count (*) AS RecordCount FROM '' + @Table + @strWhere + @strGroupBy
        EXEC sp_executesql @strSql
        --RETURN 
    END 
       
    ----------------------------------------------------------------------------------------------------
    IF @PageIndex < 1                                  --第一页提高性能
    BEGIN   
        SET @PageIndex = 1
    END

    IF @PageIndex = 1                          
    BEGIN
        SET @strSql = ''SELECT TOP '' + str(@PageSize) +  '' '' + @Field + '' FROM '' + @Table + 
                       @strWhere + @strGroupBy + @strOrderBy
        EXEC sp_executesql @strSql
        RETURN
    END
    ----------------------------------------------------------------------------------------------------  
    DECLARE @STARTID nvarchar(50)
    DECLARE @ENDID nvarchar(50)
    SET @STARTID = convert(nvarchar(50),(@PageIndex - 1) * @PageSize + 1)
    SET @ENDID = convert(nvarchar(50),@PageIndex * @PageSize)
    SET @strSql = ''WITH MYTABLE AS (SELECT ROW_NUMBER() OVER ('' + @strOrderBy + '')
                   AS RowNumber,'' + @Field + '' FROM ''+ @Table +  @strWhere + @strGroupBy + '') 
                   SELECT * FROM MYTABLE 
                   WHERE RowNumber BETWEEN '' + @STARTID + '' AND '' + @ENDID
    EXEC sp_executesql @strSql
    --------------------------------------------------------------------------------------------------
END

 

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qiwen]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[qiwen](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [varchar](100) NOT NULL,
 [ShortTitle] [varchar](50) NULL
) ON [PRIMARY]
END