对于想了解sqlserver存储过程分页的读者，本文将是一篇不可错过的文章，我们将详细介绍sqlserver存储过程分页，并且为您提供关于MSSQLSERVER通用存储过程分页、MSSQLServer
对于想了解sqlserver 存储过程分页的读者，本文将是一篇不可错过的文章，我们将详细介绍sql server 存储过程分页，并且为您提供关于MS SQLSERVER通用存储过程分页、MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）、mssqlserver存储过程分页、Sql Server 2005 存储过程分页的有价值信息。
本文目录一览：- sqlserver 存储过程分页（sql server 存储过程分页）
- MS SQLSERVER通用存储过程分页
- MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）
- mssqlserver存储过程分页
- Sql Server 2005 存储过程分页
sqlserver 存储过程分页（sql server 存储过程分页）
USE [HK_ERP_Report] GO /****** Object: StoredProcedure [dbo].[spPF_AppRpt_Tupu_CommonPage] Script Date: 2019/4/4 12:00:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* DECLARE @return_value int,@PageCount int,@TotalRecord int EXEC @return_value = [dbo].[spPF_AppRpt_Tupu_CommonPage] @TableName = N‘PF_Shopper‘,@ReFieldsstr = N‘*‘,@OrderString = N‘LastVisitTime asc‘,@PageSize = 10,@PageIndex = 1,@TotalRecord = @TotalRecord OUTPUT,@PageCount = @PageCount OUTPUT SELECT @TotalRecord as N‘@TotalRecord‘,@PageCount as N‘@PageCount‘ SELECT ‘Return Value‘ = @return_value */ ALTER PROCEDURE [dbo].[spPF_AppRpt_Tupu_CommonPage] @TableName VARCHAR(50), --表名 @ReFieldsstr VARCHAR(200) = ‘*‘, --字段名(全部字段为*) @OrderString VARCHAR(200), --排序字段(必须!支持多字段不用加order by) @WhereString VARCHAR(500) =N‘‘, --条件语句(不用加where) @PageSize INT = 10, --每页多少条记录 @PageIndex INT = 1, --指定当前为第几页 @PageCount INT = 0 OUTPUT,--总页数 @TotalRecord INT OUTPUT --返回总记录数 AS BEGIN --处理开始点和结束点 DECLARE @StartRecord INT; DECLARE @EndRecord INT; DECLARE @TotalCountsql NVARCHAR(500); DECLARE @sqlString NVARCHAR(2000); SET @StartRecord = (@PageIndex-1)*@PageSize + 1--起始记录 SET @EndRecord = @StartRecord + @PageSize - 1 --结尾记录 SET @TotalCountsql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--总记录数语句 SET @sqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘[email protected]+‘ from ‘+ @TableName;--查询语句 -- IF (@WhereString! = ‘‘ or @WhereString!=null) BEGIN SET @[email protected] + ‘ where ‘+ @WhereString; SET @sqlString [email protected]+ ‘ where ‘+ @WhereString; END --第一次执行得到 EXEC sp_executesql @totalCountsql,N‘@TotalRecord int out‘,@TotalRecord OUTPUT;--返回总记录数 SET @[email protected]/@pageSize+(CASE WHEN @TotalRecord%@pageSize>0 THEN 1 ELSE 0 END) ----执行主语句 SET @sqlString =‘select * from ‘ + @sqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ + ltrim(str(@EndRecord)); Exec(@sqlString) END
MS SQLSERVER通用存储过程分页
最近在面试的时候，遇到个奇葩的秃顶老头面试官。
问：写过存储过程分页吗？
答：没写过，但是我知道分页存储的原理，我自己也写过，只是在工作中没写过。
问：那你这么多年工作中就没写过吗？
答：的确没写过，因为项目数据量都特别小。
然后，这奇葩就起身就要走人了，连个招呼都没有。想我面试了这么多公司，这种奇葩面试官，还是头回见到，一点职业素质，礼貌都木有。气死我了！
明天要去入职新公司了，今天闲来无事，试着写了写这个存储过程分页。只在sqlSERVER2008上测试过，不过我想，2000以上版本应该都会支持的吧。
废话到此，上代码。
if (object_id('Proc_Paging', 'P') is not null)
drop proc Proc_Paging
go
create procedure Proc_Paging
(
@TableName varchar(20),--表名
@PageIndex int=0,--起始页索引，从0开始
@PageSize varchar(1000)='10',--默认每页显示的数量，默认每页显示10条记录
@Params varchar(100)='*',--分页结果中显示的字段，默认显示全部的字段
@ID varchar(20)='ID',--数据编号排序字段，默认为ID
@OrderByID varchar(20)='asc',--数据编号排序方式，默认为正序排序
@StrWhereSource varchar(100),--分页数据的查询条件，必须以where开头
@StrWherePaging varchar(100),--已经分页的数据的查询条件，不能以where开头
@OrderBy varchar(100)='ID asc'--数据排序方式
)
as
declare @sql varchar(8000),@PageStartNum varchar(1000),@PageEndNum varchar(1000),@sql2 varchar(1000),@sql3 varchar(1000)
--1.获取指定页数据
set @PageStartNum=(@PageIndex)*(@PageSize)--起始数据编号
set @PageEndNum=(@PageIndex+1)*(@PageSize)--截止数据编号
set @sql='select row_number() over(order by '+@ID+' '+@OrderByID+') Num,'+@Params+' '+
'into #temp '+
'from '+@TableName+' '+@StrWhereSource+''+
'select * from #temp where Num between '+
''+(@PageStartNum)+' and '+(@PageEndNum)+' '+@StrWherePaging+' order by '+@OrderBy+' '
exec(@sql)
--print @sql
--2.获取总记录数
set @sql2='select count(1) from '+@TableName+''
exec(@sql2)
--print @sql2
--3.获取分页总数
set @sql3='select ceiling(count(1)*1.0/('+@PageSize+')) from '+@TableName+''
exec(@sql3)
--print @sql3
go
--测试代码
exec Proc_Paging 'Customers',2,10,'CustomerID,CompanyName,Address,City,PostalCode','CustomerID','asc','where PostalCode>''1000'' ','and Num>1 ','Num asc'
go
下面是运行结果截图。
执行结果：
生成的sql语句：
大神，勿喷；菜鸟，轻拍。谢谢！
MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）
事务
事务：保证多个操作全部成功，否则全部失败，这处机制就是事务
思考：下了个订单，但是在保存详细信息时出错了，这样可以成功吗？
数据库中的事务：代码全都成功则提交，如果有某一条语句失败则回滚，整体失败
事务操作：
begin transaction--开始事务
comit transaction--提交，没错后执行
rollback transaction--回滚，出错后执行，执行了的操作会回滚，不会生效
用法：声明一个变量，记录错误信息，最终根据变量值是否大于0，进行提交或回滚
示例：模拟转账操作：一个账户扣钱，一个账户加钱
begin transaction
declare @sumError int=0
update bank set balance=balance-1000 where cid='0001'
set @sumError=@sumError+@@Error
update bank set balance=balance+1000 where cid='0002'
set @sumError=@sumError+@@Error
if(@sumError)
begin
---失败了
rollback transaction
end
else
begin
---成功了
comit transaction
end
存储过程
就是一个函数，用于存储一段处理代码
好处：
完成代码的封装，实现代码重用；
安全
方便应用程序与数据库间通信，不用传大量sql语句过程，而只用传一个存储过程名称过来，简单方便
系统存储过程（在界面中打开看一看）
自定义存储过程
create proc usp_test1
参数列表
as
自定义代码段
调用执行：exec 存储过程名称
create proc usp_TwoNumberAdd
@num1 int,
@num2 int
as
begin
select @num1+@num2
end
----存储过程传参数 有几种方式
----第一种方式
declare @n1 int=45,@n2 int=43
exec usp_TwoNumberAdd @num1=@n1,@num2=@n2
----第二种方式
exec usp_TwoNumberAdd 21,20
---模糊查询 存储过程 用户传入 宇，和年龄>20返回来有多少条数据，并把这些数据显示出来
create proc usp_SlectStuByNameAndAge
@name nvarchar(10),--名字
@age int,--年龄
@count int output --条数
as
begin
set @count=(select count(*) from Student where StuName like @name+'%' and StuAge>@age)
select * from Student where StuName like @name+'%' and StuAge>@age
end
declare @ct int
exec usp_SlectStuByNameAndAge '宇',15,@ct output
select @ct
分页的存储过程
---第几页
---每页多少条
---总页数返回来
create proc usp_pageUser
@page int,--页数
@count int,--条数
@sumPage int output--总页数
as
begin
set @sumPage=(CEILING((select count(*) from TblUsers)*1.0/@count))--总页数
select * from
(select 编号=ROW_NUMBER()over(order by AutoId),* from TblUsers)as tu
where tu.编号 between (@page-1)*@count+1 and @page*@count
end
---第4页，每页6条
declare @i int
exec usp_pageUser 4,6,@i output
select @i
触发器
作用：对表进行增、删、改操作时，自动进行一个操作
根据触发机制不同，分为：after触发器，instead of替换触发器创建触发器：
create trigger 名称
On 表名
[After|instead of] [insert|delete|update]
As
Begin
…
end
两个临时表：inserted、deleted
示例：删除分公司时，将该分公司的所有部门都删除
建议：对于性能影响太大，所以要慎重使用
mssqlserver存储过程分页
下面分享一个网友写的mssqlserver存储过程分页有需要的朋友可参考参考。
| 代码如下 | 复制代码 |
|
USE [BeyondDB] 1 else 0 end)+(case when charindex('''''')+'''''',''+@FieldOne+'')>0 then 1 else 0 end) '' else 0 end)+(case when charindex('''''')+'''''',''+@FieldTwo+'')>0 then 1 else 0 end) '' (@PageIndex + @PageSize) |
|
Sql Server 2005 存储过程分页
sql Server2005的一个新特性便是我等了很久的Row_Number(),以前用Oracle时用rownumber写分页存储过程很方便：）
下面是我做的一个小小的测试，测试我原来在sql server2000下所用的分页存储过程与使用Row_Number()编写的存储过程在sql Server2005上的执行效率...
创建数据表：
CREATE TABLE [dbo].[test](
[UserId] [int] Primary Key IDENTITY(1,1) ,
[UserName] [nvarchar](256) ,
[Sex] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Address] [varchar](100) ,
[status] [bit] NULL,
[Email] [varchar](100) ,
[InsertDate] [datetime] NOT NULL
)
插入1000k（1百万）记录
declare @n int
set @n = 0
while @n<100000
BEGIN
Insert Into test(UserName,Sex,Age,Address,status,Email,InsertDate)
Values('Name','M','25','Address',1,'EmanLeeA@gmail.com',getdate())
Select @n = @n+1
END
两个存储过程
使用Top的分页存储过程
Create proc [dbo].[test_PageById]
(
@pageIndex int,
@pageSize int
)
AS
SELECT TOP(@pageSize) *
FROM test
WHERE UserId <
(SELECT MIN(UserId) FROM (
SELECT TOP ((@pageIndex-1) * @pageSize) UserId
FROM test
ORDER BY UserId DESC)B )
ORDER BY UserId DESC
使用Row_number的存储过程
CREATE proc [dbo].[test_PageByRowNumber]
(
@pageIndex int,
@pageSize int
)
AS
DECLARE @startRow int, @endRow int
Set @startRow = (@pageIndex - 1) * @pageSize +1
SET @endRow = @startRow + @pageSize -1
SELECT*
FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNumber
FROM test ) T
WHERE T.RowNumber BETWEEN @startRow AND @endRow
测试和结果
SET STATISTICS io ON
SET STATISTICS time ON
go
EXEC test_PageByRowNumber 1000,50 /* RowNumber存储过程 */
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF
/*结果分析*/
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
(50 行受影响)
表 'test'。扫描计数 1，逻辑读取 50098 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
sql Server 执行时间:
cpu 时间 = 219 毫秒，占用时间 = 213 毫秒。
sql Server 执行时间:
cpu 时间 = 219 毫秒，占用时间 = 213 毫秒。
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
测试和结果
SET STATISTICS io ON
SET STATISTICS time ON
go
EXEC test_PageById 1000,50 --执行使用top语句的存储过程
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF
/*结果分析*/
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
(50 行受影响)
表 'test'。扫描计数 2，逻辑读取 153 次，物理读取 0 次，预读 93 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
sql Server 执行时间:
cpu 时间 = 16 毫秒，占用时间 = 26 毫秒。
sql Server 执行时间:
cpu 时间 = 16 毫秒，占用时间 = 26 毫秒。
sql Server 分析和编译时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 1 毫秒。
至此可以很直观的看出差距了.使用原来的Top子句的存储过程比使用Row_Number()的存储过程执行时间快了将近10倍.....
其实直接分析语句也可以看出，Row_Number()的效率不会是最高的，因为它必须先为100万条记条生成RowNumber,自然不会快到哪里去了.
不过前者的适应范围有些限制，即必须有一个为数字的唯一字段，如果使用uniqueidentifier为主键的话则不能使用了.
Row_Number分页有很好的通用性和直观易用性，对于数据量较少来说，二者应该不会有很大的区别，使用哪个就看你自己的需要了.
http://blog.csdn.net/showsunrise/archive/2008/09/08/2901627.aspx
今天关于sqlserver 存储过程分页和sql server 存储过程分页的分享就到这里，希望大家有所收获，若想了解更多关于MS SQLSERVER通用存储过程分页、MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）、mssqlserver存储过程分页、Sql Server 2005 存储过程分页等相关知识，可以在本站进行查询。
本文标签：
