伟德手机版伟德娱乐一个通用的分页存储过程(原

2019-11-15 21:11 来源:未知

if exists(select * from sys.objects where name='存储过程名称')
drop proc 存储过程名称
go
CREATE proc 存储过程名称
@tableName varchar(8000),          --表名、视图名
@indexCol varchar(50) = 'a.id',      --标识列名(如:比如主键、标识,推荐使用索引列)
@pageSize int = 10,                --页面大小
@pageIndex int = 0,                --当前页
@orderCol varchar(100) = 'a.id desc',--排序 (如:id)
@where varchar(max) = '',         --条件
@columns varchar(500) = '*'        --要显示的列
as
declare @sql varchar(max)
declare @sql2 varchar(max)
declare @where2 varchar(max)

  • 目标

2006年6月14日  作者:meil  博客:meil.livebaby.cn

这个只要按照给定参数就可以实现分页效果,不过一次返回笔数是有调用者确定

  新手一个,写的最简单的存储过程分页,上代码吧,还请大神们多多调教!

if @where <> ''
begin
    select @where2 = ' And ' @where
    select @where = ' Where ' @where
end
else
    select @where2 = ''

  1. 实现简单的数据库编程
  2. 实现通用的删除功能
  3. 实现通用的修改功能

本文版权归作者meil转载请注明出处 

伟德手机版伟德娱乐 1伟德手机版伟德娱乐 2Code
 1伟德手机版伟德娱乐 3SET QUOTED_IDENTIFIER ON 
 2伟德手机版伟德娱乐 4GO
 3伟德手机版伟德娱乐 5SET ANSI_NULLS ON 
 4伟德手机版伟德娱乐 6GO
 5伟德手机版伟德娱乐 7
 6伟德手机版伟德娱乐 8
 7伟德手机版伟德娱乐 9ALTER   PROCEDURE GetRecords 
 8伟德手机版伟德娱乐 10(@TableName varchar(15),             ---表名
 9伟德手机版伟德娱乐 11@KeyName varchar(30),            ---key值欄位名
10伟德手机版伟德娱乐 12@ReturnFieldName varchar(300),        ---返回欄位名集合
11伟德手机版伟德娱乐 13@Condition Varchar(1000),            ---條件集合
12伟德手机版伟德娱乐 14@Order varchar(1000),            ---排序集合
13伟德手机版伟德娱乐 15@RecordNum int,                 ---返回一頁的記錄數
14伟德手机版伟德娱乐 16@StartNO int,                ---開始記錄數
15伟德手机版伟德娱乐 17@@RealNum INT OUTPUT,             ---真實一頁返回筆數
16伟德手机版伟德娱乐 18@@TotalCount INT OUTPUT  )        ---總共筆數
17伟德手机版伟德娱乐 19AS
18伟德手机版伟德娱乐 20
19伟德手机版伟德娱乐 21DECLARE @PreRecCount VARCHAR( 10 )    ---上次的筆數
20伟德手机版伟德娱乐 22DECLARE @CurRecCount VARCHAR( 10 )    ---當前的筆數
21伟德手机版伟德娱乐 23
22伟德手机版伟德娱乐 24
23伟德手机版伟德娱乐 25declare @ReturnTotal int     ---回傳的總筆數             
24伟德手机版伟德娱乐 26DECLARE @SQLStr NVARCHAR(500)            ---回傳總筆數的SQL語句
25伟德手机版伟德娱乐 27set @SQLStr=N'SELECT @ReturnTotal=COUNT(*) FROM '        ---因為這個參數要求回傳所以處理不一樣,前者把變量寫在SQL語句中
26伟德手机版伟德娱乐 28set @SQLStr=@SQLStr  @TableName  ' WHERE 1=1 '  @Condition    ---直接讓變量轉化為值
27伟德手机版伟德娱乐 29exec sp_executesql @SQLStr,N'@ReturnTotal  int output',
28伟德手机版伟德娱乐 30@ReturnTotal  output
29伟德手机版伟德娱乐 31Set @@TotalCount=@ReturnTotal
30伟德手机版伟德娱乐 32
31伟德手机版伟德娱乐 33----返回該頁可以返回的真實筆數
32伟德手机版伟德娱乐 34IF @@TotalCount > ( @StartNO   1 ) * @RecordNum
33伟德手机版伟德娱乐 35    SET @@RealNum = @RecordNum            
34伟德手机版伟德娱乐 36ELSE
35伟德手机版伟德娱乐 37    SET @@RealNum = @@TotalCount - @StartNO * @RecordNum
36伟德手机版伟德娱乐 38
37伟德手机版伟德娱乐 39SET @CurRecCount = CAST( @StartNO * @RecordNum   @@RealNum AS VARCHAR( 10 ) )
38伟德手机版伟德娱乐 40IF @STARTNO = 0
39伟德手机版伟德娱乐 41   EXEC( 'SELECT TOP '   @RecordNum   @ReturnFieldName '   FROM ' @TableName ' WHERE 1=1  ' @Condition ' Order By ' @Order )
40伟德手机版伟德娱乐 42ELSE
41伟德手机版伟德娱乐 43   BEGIN
42伟德手机版伟德娱乐 44    SET @PreRecCount = CAST( @StartNO * @RecordNum AS VARCHAR( 10 ) )
43伟德手机版伟德娱乐 45    EXEC( 'SELECT TOP '   @RecordNum  '  '  @ReturnFieldName  '   FROM ' @TableName ' WHERE 1=1  ' @Condition ' AND ' @KeyName ' NOT IN  ' 
44伟德手机版伟德娱乐 46          '(SELECT TOP '   @PreRecCount   @KeyName '  FROM  ' @TableName ' WHERE 1=1  ' @Condition ' Order By ' @Order ')'  ' Order By ' @Order) 
45伟德手机版伟德娱乐 47   END
46伟德手机版伟德娱乐 48
47伟德手机版伟德娱乐 49print @PreRecCount
48伟德手机版伟德娱乐 50print @CurRecCount
49伟德手机版伟德娱乐 51
50伟德手机版伟德娱乐 52GO
51伟德手机版伟德娱乐 53SET QUOTED_IDENTIFIER OFF 
52伟德手机版伟德娱乐 54GO
53伟德手机版伟德娱乐 55SET ANSI_NULLS ON 
54伟德手机版伟德娱乐 56GO
55伟德手机版伟德娱乐 57
56伟德手机版伟德娱乐 58

  

select @sql = 'Select Top ' Convert(varchar(10),@pageSize) ' ' @columns ' From ' @tableName
select @sql2 = @sql @where
select @sql =  @sql ' Where ' '(' @indexCol ' Not In (Select Top ' Convert(varchar(10),  ((@pageIndex-1)*@pageSize)) ' ' @indexCol

  • 通用删除和通用修改 实现思路

        这是我项目中使用的一个分页存储过程,具有很强的通用性。配合前台ASP.NET使用50万条数据基本感不到延迟。数据库为SQLServer2000。

伟德手机版伟德娱乐 59伟德手机版伟德娱乐 60

  • ' From ' @tableName @where   ' Order by ' @orderCol '))'
    select @sql = @sql @where2
    select @sql = @sql ' Order by ' @orderCol
    --获取数据集
    exec (@sql)
    PRINT @sql
    select @sql2 = Replace(@sql2,'Top ' Convert(varchar(10), @pageSize) ' ' @columns, 'count(1)')
    --获取总数据条数
    exec(@sql2)
  1. 普通删除功能

 

 1 USE [BMS]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[Pages]    Script Date: 12/21/2015 11:48:57 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER PROCEDURE [dbo].[Pages]
 9     @page int, --当前页
10     @rows int, --每页显示行数
11     @sidx varchar(50), --排序字段
12     @sord varchar(10), --排序规则
13     @tablename varchar(50), --表名
14     @where varchar(200), --查询条件
15     @id varchar(50) --表的主键字段名
16 AS
17 declare @sqlstr varchar(500) --拼接sql语句
18 BEGIN
19     SET NOCOUNT ON;
20     set @sqlstr='select top ' CAST(@rows AS varchar) ' * from ' @tablename ' where 1=1 ' @where
21      'and ' @id ' not in (select top ' CAST(((@page-1)*@rows) AS varchar) ' ' @id ' from ' @tablename ' order by ' @sidx ' ' @sord ') order by ' @sidx ' ' @sord '';
22     --print @sqlstr
23     EXEC(@sqlstr)
24 END;

GO

    根据某个条件删除某个表信息,通常如:根据主键删除新闻表信息某条数据,根据主键删除用户表某条数据

1. 分页存储过程(源码)

View Code

  分析:

CREATE   procedure pagination

 

    如果要实现通用的删除功能,只需要编写一个存储过程,传入要删除的表名和删除的条件即可,拼接一个sql语句,最后执行这个sql语句,实现通用的删除功能

 @str_sql           varchar(1000) = '*',     -- 执行的SQL 不含Order by 内容  
 @str_orderfield    varchar(255)='''',       -- 排序的字段名 
 @page_size         int = 10,                     -- 页大小 
 @page_index        int = 0,                      -- 页码
 @order_type        int,                           -- 设置排序类型, 非 -1 值则降序 
 @total_count       int   output                 -- 返回记录总数, 非 0 值则返回 
as

  • 通用修改功能实现思路

  1. 普通修改:一个表就需要写一个存储过程去实现修改功能

-- 获取指定页的数据--

declare @strsql   varchar(5000)              -- 主语句
declare @strtmp   varchar(5000)             -- 临时变量
declare @strorder varchar(400)              -- 排序字串
declare @cruRow   int                            -- 当前行号  

--执行总数统计
exec getRowCount @str_sql,@total_count output

set @strtmp =  ' select * from '
        '      (select top ' convert(varchar(10),@page_size) ' * from '
        '         (select top ' convert(varchar(10),(@page_index 1) * @page_size)  ' * from '        -- N 1页

--排序方向
if @order_type !=0
 begin
 set @strsql= @strtmp
       '          order by @str_orderfield asc) a '
       '       order by @str_orderfield desc)b'
              ' order by @str_orderfield asc'
 end
else
 begin
 set @strsql= @strtmp
       '          order by @str_orderfield desc) a '
       '       order by  @str_orderfieldasc)b'
              ' order by  @str_orderfield desc'
 end

exec (@strsql)

GO


 2. 分页存储过程(源码) 执行中用到的行数统计

create  procedure getRowCount
       @sql    nvarchar(2000),
       @count  int output
as
begin


  分析:

-- 获取数据总行数 --

  declare @tmpsql nvarchar(2000)
  set @tmpsql='select @count=count(*)  from (' @sql ') a'

  execute sp_executesql @tmpsql,N'@count int output',@count output
 
end

GO

   是否可以直接将表名,修改的列名,修改的条件作为参数,拼接一个sql语句,最后执行这个sql语句,实现通用的修改功能

  • 执行sql语句

 EXEC(@sql)

通用删除功能如下

-- =============================================
-- Author:        <Author,,Name>
-- Create date: 2014年4月12日 23:04:30
-- Description:    <通用删除>
-- =============================================
CREATE PROCEDURE DelTable]    
    @tablename nvarchar(100),--表名
    @condition nvarchar(200)--查询条件
AS
BEGIN
--拼接sql
    DECLARE @sql nvarchar(1000)
    SET @sql='Delete from ' @tablename ' where ' @condition
    EXEC(@sql)
END

测试打印sql如下
Delete from userinfo where 3

在t-sql界面操作调用格式如下

EXEC DelTable 'userinfo','id=6'

通用修改功能如下

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <2014年4月12日 23:33:39
-- Description:    通用修改
-- =============================================
CREATE PROCEDURE UpdateTable
    @tablename nvarchar(100),
    @columns nvarchar(200),
    @condition nvarchar(500)
AS
BEGIN
    DECLARE @sql nvarchar(1000)
    SET @sql='update ' @tablename ' set ' @columns ' where '  @condition
    EXEC(@sql)
END
GO

在界面调用如下

EXEC [dbo].[UpdateTable] 'userinfo','age=24','id=1'

通用查询功能如下

-- =============================================
-- Author:        <Author,,Name>
-- Create date: 2014年4月13日 00:07:44
-- Description:    查询
-- =============================================
CREATE PROCEDURE QueryTable 
    @tablename nvarchar(100),
    @column nvarchar(200),
    @condition nvarchar(300)
AS
BEGIN
    DECLARE @sql nvarchar(1000)
    SET @sql='select ' @column  ' from ' @tablename ' where 1=1' @condition
    EXEC(@sql)
END
GO

调用

EXEC [dbo].[QueryTable] 'userinfo','*',''

 

版权声明:本文由19463331韦德国际发布于计算机网络,转载请注明出处:伟德手机版伟德娱乐一个通用的分页存储过程(原