创建测试表，批量插入数据的存储过程，分页存储过程

 

 

-------------------------------------------------------------------
--创建测试数据库表
-------------------------------------------------------------------
CREATE TABLE [UserList]
    (
      [id] INT PRIMARY KEY
               IDENTITY(1, 1) ,
      [userName] NVARCHAR(50) ,
      [passWord] NVARCHAR(255) ,
      [userRealName] NVARCHAR(50) ,
      [userTel] NVARCHAR(50) ,
      [userState] INT ,
      [userMail] NVARCHAR(50) ,
      [userLastLogin] DATETIME ,
      [userPost] NVARCHAR(50) ,
      [AddTime] DATETIME
    )
GO
-------------------------------------------------------------------
--批量插入测试用户数据信息
-------------------------------------------------------------------
DECLARE @i INT
SET @i = 1
WHILE @i <= 100 
    BEGIN
        INSERT  INTO [UserList]
                ( [userName] ,
                  [passWord] ,
                  [userRealName] ,
                  [userTel] ,
                  [userState] ,
                  [userMail] ,
                  [userLastLogin] ,
                  [userPost] ,
                  [AddTime]
                )
        VALUES
                ( @i ,
                  '123456' ,
                  'liangjw' + CONVERT(NVARCHAR(20), @i) ,
                  '15527808832' + CONVERT(NVARCHAR(20), @i) ,
                  '0' ,
                  'liangjw0504@163.com' ,
                  GETDATE() ,
                  '超级管理员' ,
                  GETDATE()
                )
        SET @i = @i + 1
    END

GO
-------------------------------------------------------------------
--创建分页存储过程
-------------------------------------------------------------------
CREATE PROC [dbo].[sp_PageView]
    @tbname SYSNAME ,               --要分页显示的表名   
    @FieldKey NVARCHAR(1000) ,      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段   
    @PageCurrent INT = 1 ,               --要显示的页码   
    @PageSize INT = 10 ,                --每页的大小(记录数)   
    @FieldShow NVARCHAR(1000) = '' ,      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段   
    @FieldOrder NVARCHAR(1000) = '' ,      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC   用于指定排序顺序   
    @Where NVARCHAR(1000) = '' ,     --查询条件   
    @RecordCount INT OUTPUT             --总页数   
AS 
    DECLARE @PageCount AS INT
    SET NOCOUNT ON   
--检查对象是否有效   
    IF OBJECT_ID(@tbname) IS NULL 
        BEGIN   
            RAISERROR(N'对象"%s"不存在',1,16,@tbname)   
            RETURN   
        END   
    IF OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsTable') = 0
        AND OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsView') = 0
        AND OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsTableFunction') = 0 
        BEGIN   
            RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)   
            RETURN   
        END   
   
--分页字段检查   
    IF ISNULL(@FieldKey, N'') = '' 
        BEGIN   
            RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)   
            RETURN   
        END   
   
--其他参数检查及规范   
    IF ISNULL(@PageCurrent, 0) < 1 
        SET @PageCurrent = 1   
    IF ISNULL(@PageSize, 0) < 1 
        SET @PageSize = 10   
    IF ISNULL(@FieldShow, N'') = N'' 
        SET @FieldShow = N'*'   
    IF ISNULL(@FieldOrder, N'') = N'' 
        SET @FieldOrder = N''   
    ELSE 
        SET @FieldOrder = N'ORDER BY ' + LTRIM(@FieldOrder)   
    IF ISNULL(@Where, N'') = N'' 
        SET @Where = N''   
    ELSE 
        SET @Where = N'WHERE (' + @Where + N')'   
   
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)   
    IF @PageCount IS NULL 
        BEGIN   
            DECLARE @sql NVARCHAR(4000)   
            SET @sql = N'SELECT @RecordCount=COUNT(*)' + N' FROM ' + @tbname
                + N' ' + @Where   
            EXEC sp_executesql 
                @sql ,
                N'@RecordCount int OUTPUT' ,
                @RecordCount OUTPUT   

            SET @PageCount = ( @RecordCount + @PageSize - 1 ) / @PageSize   
        END   
   
--计算分页显示的TOPN值   
    DECLARE
        @TopN VARCHAR(20) ,
        @TopN1 VARCHAR(20)   
    SELECT
        @TopN = @PageSize ,
        @TopN1 = ( @PageCurrent - 1 ) * @PageSize   
   
--第一页直接显示   
    IF @PageCurrent = 1 
        EXEC(N'SELECT TOP '+@TopN   
        +N' '+@FieldShow   
        +N' FROM '+@tbname   
        +N' '+@Where   
        +N' '+@FieldOrder)   
    ELSE 
        BEGIN   
    --处理别名   
            IF @FieldShow = N'*' 
                SET @FieldShow = N'a.*'   
   
    --生成主键(惟一键)处理条件   
            DECLARE
                @Where1 NVARCHAR(4000) ,
                @Where2 NVARCHAR(4000) ,
                @s NVARCHAR(1000) ,
                @Field SYSNAME   
            SELECT
                @Where1 = N'' ,
                @Where2 = N'' ,
                @s = @FieldKey   
            WHILE CHARINDEX(N',', @s) > 0 
                SELECT
                    @Field = LEFT(@s, CHARINDEX(N',', @s) - 1) ,
                    @s = STUFF(@s, 1, CHARINDEX(N',', @s), N'') ,
                    @Where1 = @Where1 + N' AND a.' + @Field + N'=b.' + @Field ,
                    @Where2 = @Where2 + N' AND b.' + @Field + N' IS NULL' ,
                    @Where = REPLACE(@Where, @Field, N'a.' + @Field) ,
                    @FieldOrder = REPLACE(@FieldOrder, @Field, N'a.' + @Field) ,
                    @FieldShow = REPLACE(@FieldShow, @Field, N'a.' + @Field)   
            SELECT
     --@Where=REPLACE(@Where,@s,N'a.'+@s),   
                @FieldOrder = REPLACE(@FieldOrder, @s, N'a.' + @s) ,
                @FieldShow = REPLACE(@FieldShow, @s, N'a.' + @s) ,
                @Where1 = STUFF(@Where1 + N' AND a.' + @s + N'=b.' + @s, 1, 5,
                                N'') ,
                @Where2 = CASE WHEN @Where = '' THEN N'WHERE ('
                               ELSE @Where + N' AND ('
                          END + N'b.' + @s + N' IS NULL' + @Where2 + N')'   
    --执行查询 
  
            EXEC(N'SELECT TOP '+@TopN   
            +N' '+@FieldShow   
            +N' FROM '+@tbname   
            +N' a LEFT JOIN(SELECT TOP '+@TopN1   
            +N' '+@FieldKey   
            +N' FROM '+@tbname   
            +N' a '+@Where   
            +N' '+@FieldOrder   
            +N')b ON '+@Where1   
            +N' '+@Where2   
            +N' '+@FieldOrder)   
        END