MSSQL之二十一 存储过程案例
【摘要】
Stored Procedure（存储过程）编写经验和优化措施 一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对Sybase和SQL Server数据库，但其它数据库应该有一些共性。 二、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储...
-
Stored Procedure（存储过程）编写经验和优化措施
-
-
-
一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对Sybase和SQL Server数据库，但其它数据库应该有一些共性。
-
-
-
二、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。
-
-
-
三、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。
-
-
-
四、内容：
-
-
-
1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。
-
-
-
2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
-
-
-
3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：
-
-
-
a) SQL的使用规范：
-
-
-
i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
-
ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
-
iii.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
-
iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
-
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
-
vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
-
vii.尽量使用“>=”，不要使用“>”。
-
viii.注意一些or子句和union子句之间的替换
-
ix.注意表之间连接的数据类型，避免不同类型数据之间的连接。
-
x. 注意存储过程中参数和数据类型的关系。
-
xi.注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。
-
-
-
-
b) 索引的使用规范：
-
i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
-
ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
-
iii.避免对大表查询时进行table scan，必要时考虑新建索引。
-
iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
-
v. 要注意索引的维护，周期性重建索引，重新编译存储过程。
-
-
-
c)tempdb的使用规范：
-
i. 尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。
-
ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。
-
iii.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
-
iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
-
v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
-
vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
-
-
-
d)合理的算法使用：
-
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。
-
===============================
-
金额阿拉伯数字转换为中文的自定义函数
-
CREATE FUNCTION ChangeBigSmall
-
(@ChangeMoney money)
-
RETURNS VarChar(100) AS
-
-
BEGIN
-
Declare @String1 char(20)
-
Declare @String2 char(30)
-
Declare @String4 Varchar(100)
-
Declare @String3 Varchar(100) --从原A值中取出的值
-
Declare @i int --循环变量
-
Declare @J Int --A的值乘以100的字符串长度
-
Declare @Ch1 Varchar(100) --数字的汉语读法
-
Declare @Ch2 Varchar(100) --数字位的汉字读法
-
Declare @Zero Int --用来计算连续有几个零
-
Declare @Returnvalue VarChar(100)
-
-
Select @Returnvalue = ''
-
Select @String1 = '零壹贰叁肆伍陆柒捌玖'
-
Select @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分'
-
-
Select @String4 = Cast(@ChangeMoney*100 as int)
-
-
select @J=len(cast((@ChangeMoney*100) as int))
-
-
Select @String2=Right(@String2,@J)
-
-
-
Select @i = 1
-
-
while @i<= @j Begin
-
-
Select @String3 = Substring(@String4,@i,1)
-
-
if @String3<>'0' Begin
-
Select @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)
-
Select @Ch2 = Substring(@String2, @i, 1)
-
Select @Zero = 0 --表示本位不为零
-
end
-
else Begin
-
If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)
-
Select @Ch1 = '零'
-
Else
-
Select @Ch1 = ''
-
Select @Zero = @Zero + 1 --表示本位为0
-
-
--如果转换的数值需要扩大，那么需改动以下表达式 I 的值。
-
Select @Ch2 = ''
-
If @i = @J - 10 Begin
-
Select @Ch2 = '亿'
-
Select @Zero = 0
-
end
-
-
If @i = @J - 6 Begin
-
Select @Ch2 = '万'
-
Select @Zero = 0
-
end
-
-
if @i = @J - 2 Begin
-
Select @Ch2 = '元'
-
Select @Zero = 0
-
end
-
-
If @i = @J
-
Select @Ch2 = '整'
-
-
end
-
Select @Returnvalue = @Returnvalue + @Ch1 + @Ch2
-
select @i = @i+1
-
end
-
-
-
--最后将多余的零去掉
-
If CharIndex('仟仟',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '仟仟', '仟')
-
-
-
If CharIndex('佰佰',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '佰佰', '佰')
-
-
-
If CharIndex('零元',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零元', '元')
-
-
If CharIndex('零万',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零万', '万')
-
-
If CharIndex('零亿',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零亿', '亿')
-
-
If CharIndex('零整',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零整', '整')
-
-
If CharIndex('零佰',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零佰', '零')
-
-
-
If CharIndex('零仟',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '零仟', '零')
-
-
-
If CharIndex('元元',@Returnvalue) <> 0
-
Select @Returnvalue = Replace(@Returnvalue, '元元', '元')
-
return @Returnvalue
-
END
-
===========================================
-
MS SQL SERVER 图像或大文本的输入输出
-
在MS SQL SERVER 安装目录下有个可执行文件叫 TEXTCOPY.EXE
-
可对 MS SQL SERVER 中的文本或图像数据进行输入输出.
-
不过你可以在MS-DOS方式下执行textcopy /? 得到它的描述。
-
下面是这个工具的描述:
-
Copies a single text or image value into or out of SQL Server. The val
-
ue
-
is a specified text or image 'column' of a single row (specified by th
-
e
-
"where clause") of the specified 'table'.
-
-
-
If the direction is IN (/I) then the data from the specified 'file' is
-
-
-
copied into SQL Server, replacing the existing text or image value. If
-
the
-
direction is OUT (/O) then the text or image value is copied from
-
SQL Server into the specified 'file', replacing any existing file.
-
-
-
TEXTCOPY [/S ][sqlserver]] [/U [login]] [/P ][password]]
-
[/D ][database]] [/T table] [/C column] [/W"where clause"]
-
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]
-
-
-
/S sqlserver The SQL Server to connect to. If 'sqlserver' is n
-
ot
-
specified, the local SQL Server is used.
-
/U login The login to connect with. If 'login' is not spec
-
ified,
-
a trusted connection will be used.
-
/P password The password for 'login'. If 'password' is not
-
specified, a NULL password will be used.
-
/D database The database that contains the table with the tex
-
t or
-
image data. If 'database' is not specified, the d
-
efault
-
database of 'login' is used.
-
/T table The table that contains the text or image value.
-
-
-
/C column The text or image column of 'table'.
-
/W "where clause" A complete where clause (including the WHERE keyw
-
ord)
-
that specifies a single row of 'table'.
-
/F file The file name.
-
/I Copy text or image value into SQL Server from 'fi
-
le'.
-
/O Copy text or image value out of SQL Server into '
-
file'.
-
/K chunksize Size of the data transfer buffer in bytes. Minimu
-
m
-
value is 1024 bytes, default value is 4096 bytes.
-
-
-
/Z Display debug information while running.
-
/? Display this usage information and exit.
-
-
-
You will be prompted for any required options you did not specify.
-
-
为此, 可写一个存储过程,调用这个命令
-
CREATE PROCEDURE sp_textcopy (
-
@srvname varchar (30),
-
@login varchar (30),
-
@password varchar (30),
-
@dbname varchar (30),
-
@tbname varchar (30),
-
@colname varchar (30),
-
@filename varchar (30),
-
@whereclause varchar (40),
-
@direction char(1))
-
AS
-
DECLARE @exec_str varchar (255)
-
SELECT @exec_str =
-
'textcopy /S ' + @srvname +
-
' /U ' + @login +
-
' /P ' + @password +
-
' /D ' + @dbname +
-
' /T ' + @tbname +
-
' /C ' + @colname +
-
' /W "' + @whereclause +
-
'" /F ' + @filename +
-
' /' + @direction
-
EXEC master..xp_cmdshell @exec_str
-
-
-
下面是一个拷贝图像到SQL Server的pubs数据库的例子, 表名pub_info, 字段名
-
logo，图像文件名picture.bmp，保存到pub_id='0736'记录 sp_textcopy @srvn
-
ame = 'ServerName',
-
@login = 'Login',
-
@password = 'Password',
-
@dbname = 'pubs',
-
@tbname = 'pub_info',
-
@colname = 'logo',
-
@filename = 'c:\picture.bmp',
-
@whereclause = " WHERE pub_id='0736' ",
-
@direction = 'I'
-
-
-
-
-
select 联系人,订购日期,订购数量,产品名称
-
from 客户信息表 a,订单信息表 b,产品信息表 c
-
where a.客户编号=b.客户编号 and b.产品编号=c.产品编号
-
-
-
-
select 联系人,订购日期,订购数量,产品名称
-
from 客户信息表 a inner join 订单信息表 b
-
on a.客户编号=b.客户编号 inner join 产品信息表 c
-
on b.产品编号=c.产品编号
-
-
-
---------------------------------------------
-
use 范例
-
go
-
-
--创建存储过程
-
create procedure Query
-
as
-
select 联系人,订购日期,订购数量,产品名称
-
from 客户信息表 a,订单信息表 b,产品信息表 c
-
where a.客户编号=b.客户编号 and b.产品编号=c.产品编号
-
-
--执行存储过程 sp_recompile作用：对自己定义的存储过程进行重新编译
-
exec sp_recompile Query
-
-
exec Query
-
-
--删除存储过程
-
drop procedure Query
-
-
select * from master.dbo.syscomments
-
-
--查看存储过程源代码
-
exec sp_helptext sp_renamedb
-
-
--修改存储过程
-
-
alter procedure Query
-
with encryption
-
as
-
select 联系人,订购日期,订购数量,产品名称
-
from 客户信息表 a,订单信息表 b,产品信息表 c
-
where a.客户编号=b.客户编号 and b.产品编号=c.产品编号
-
-
--====================================================================
-
-- 输出参数
-
create proc QueryName
-
@ProductID char(10), --输入参数
-
@ProductName char(10) output -- 输出参数
-
as
-
select @ProductName=产品名称 from 产品信息表
-
where 产品编号 = @ProductID
-
--return @ProductName
-
go
-
-
select * from 产品信息表
-
-
--执行
-
-- declare @prodName varchar(25)
-
-- exec QueryName 'P003',@ProductName=@prodName OUTPUT
-
--
-
-- print @prodName
-
declare @prodName varchar(25)
-
exec QueryName 'P003' ,@prodName output
-
print @prodName
-
-
-
--=====================================
-
-
create proc abc
-
as
-
begin tran
-
print 'fs'
-
-
if @@error > 0 --or @@rowcount <> 1
-
goto seed
-
-
print 'fs'
-
-
seed:
-
if @@error > 0 --or @@rowcount <> 1
-
begin
-
--select @@error
-
--select @@rowcount as ssss
-
rollback tran
-
print '发生错误提交无法完成！！！！'
-
end
-
else
-
begin
-
commit tran
-
print '无错误发生提交正常！！！'
-
end
-
-
go
-
-
--==============================================================
-
exec abc
-
-
drop proc abc
-
/*
-
存储过程:
-
将常用的或很复杂的工作，预先以sql程序写好，
-
然后指定一个程序名称保存起来，那么以后只要使用execute指令
-
来执行这个程序，即可自动完成该项工作。
-
-
优点：
-
1.存储过程是预先编译的，执行效率高。
-
2.存储过程的执行是在服务器端，这样就可极大地降低网络流量。
-
3.重复使用。
-
4.如果所有客户端都通过存储过程来进行访问数据，这
-
样可禁止对物理表的直接访问，提高数据安全性。
-
-
存储过程的种类：
-
-
1.系统存储过程。 以sp_开头
-
2.扩展存储过程。 以xp_开头
-
3.用户定义存储过程。
-
*/
-
-
use master
-
exec xp_cmdshell 'dir C:'
-
-
-
-------------------------------------------------------------------------------------------------------------------------------
-
-
create table 标标公司
-
(
-
产品名称 varchar(50) not null,
-
价格 smallmoney not null
-
)
-
-
create table 旗旗公司
-
(
-
产品名称 varchar(50) not null,
-
价格 smallmoney not null
-
)
-
-
insert 标标公司 (产品名称,价格) values ('原子笔',10)
-
insert 标标公司 (产品名称,价格) values ('钢笔',90)
-
insert 标标公司 (产品名称,价格) values ('荧光笔',40)
-
insert 标标公司 (产品名称,价格) values ('水彩笔',60)
-
-
insert 旗旗公司 (产品名称,价格) values ('原子笔',10)
-
insert 旗旗公司 (产品名称,价格) values ('钢笔',100)
-
insert 旗旗公司 (产品名称,价格) values ('2B铅笔',20)
-
insert 旗旗公司 (产品名称,价格) values ('毛铅笔',50)
-
-
select * from 标标公司
-
select * from 旗旗公司
-
-
truncate table 标标公司
-
-
-
-----------------------------------------建立存储过程----------------------------------------------------
-
alter procedure myproc1
-
with encryption --encryption对存储过程进行加密
-
as
-
if 1<2
-
print 'AAAAA'
-
select * from 标标公司
-
where 价格 > 15
-
go
-
-
------------------
-
exec myproc1
-
-----------------------------------------建立视图--------------------------------------------------------
-
alter view myview1
-
with encryption
-
as
-
select * from 标标公司
-
where 价格 > 15
-
go
-
---------------------------
-
select * from myview1
-
-
------------------------建立存储过程并设置参数------------------------------
-
create procedure myproc2
-
@param1 char(10)=null,
-
@param2 money=NULL
-
--with encryption
-
as
-
insert 标标公司 (产品名称,价格) values (@param1,@param2)
-
go
-
-
--================================================================
-
exec myproc2 '铅笔盒',120 -- 执行存储过程，并给定参数
-
go
-
--=============================================================
-
-
drop procedure myproc2 -- 删除存储过程
-
-
select * from 标标公司
-
-
-------------------------------------------
-
-------------------------------------------
-
-
create proc myproc3
-
@param1 char(10) =null,
-
@param2 money = null,
-
@param3 money output
-
as
-
insert 标标公司 (产品名称,价格) values (@param1,@param2)
-
-
select @param3 = sum(价格)
-
from 标标公司
-
where 价格 > 15
-
go
-
-------------------------------------------------------
-
------------------------------------
-
-- 执行myproc3存储过程，并将返回值指定给@sum.
-
declare @sum money
-
exec myproc3 '金笔',1200,@sum output
-
print '目前的总价格为: ' + convert(varchar,@sum)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
select * from 标标公司
-
-------------------------------------
-
-------------------------------------建立群组存储过程-------------------------
-
create proc myproc5;1
-
as
-
select * from 旗旗公司
-
go
-
-
create proc myproc5;2
-
as
-
select * from 标标公司
-
go
-
-
-
exec myproc5;1
-
-
exec myproc5;2
-
-
drop proc myproc5
-
--------------------------------------------修改存储过程-------------------------------------------------
-
-
alter procedure myproc2
-
@param1 char(10),
-
@param2 money
-
with encryption
-
as
-
insert 标标公司 (产品名称,价格) values (@param1,@param2)
-
go
-
-
-----------------------------------------------------------------------------------------------------
-
drop proc myproc
-
-
create proc myproc
-
as
-
declare @v int
-
select @v=5
-
while @v>0
-
begin
-
select @v*@v
-
select @v=@v-1
-
if @v=2
-
begin
-
raiserror('计数器已小于3',16,5)
-
--break
-
return
-
end
-
end
-
print 'wwwwwwwwwwwwwwwww'
-
-
-
exec myproc
-
这个存储过程执行的速度还不错.500W速度分页只要2秒,任何页数
-
可是有点问题就是,如果排序不是主键,ID,那么分页就会出错,不知道各位有什么好个意见,修改一下
-
-------------------------------------------------------
-
create procedure SqlDataPaging
-
@tbName varchar(255), --表名
-
@tbFields varchar(1000), --返回字段
-
@OrderField varchar(255), --排序的字段名
-
@PageSize int, --页尺寸
-
@PageIndex int, --页码
-
@OrderType bit, --排序类型,1是升序,0是降序
-
@strWhere varchar(1500), --查询条件
-
@Total int output --返回总记录数
-
as
-
declare @strSql varchar(5000) --主语句
-
declare @strOrder varchar(200) --排序
-
declare @strSqlCount nvarchar(500)--查询记录总数主语句
-
declare @strtemp varchar(50) --排序附加语句
-
--------------排序-----------------1是降序,0未升序
-
if @OrderType !=0
-
begin
-
set @strtemp= '>(select max('
-
set @strOrder='order by ' + @OrderField + ' asc '
-
end
-
else
-
begin
-
set @strtemp= '<(select min('
-
set @strOrder='order by '+ @OrderField + ' desc'
-
end
-
--------------总记录数---------------
-
if @strWhere !=''
-
begin
-
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + 'where '+ @strWhere
-
end
-
else
-
begin
-
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
-
end
-
--------------如果是第一页------------
-
if @PageIndex =1
-
begin
-
if @strWhere !=''
-
begin
-
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
-
end
-
else
-
begin
-
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
-
end
-
end
-
else
-
begin
-
if @strWhere !=''
-
begin
-
set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields
-
+' from ' + @tbName + ' where '+@OrderField +' '
-
+ @strtemp+ ' ' +@OrderField+ ') from (select top'
-
+str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbName
-
+ @strWhere+ ' '+ @strOrder +') as tb)'+ @strOrder
-
end
-
else
-
begin
-
set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields
-
+' from ' + @tbName + ' where '+@OrderField + ' '
-
+ @strtemp+ ' ' +@OrderField+ ') from (select top'
-
+str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbName
-
+ ' '+ @strOrder +') as tb)'+ @strOrder
-
end
-
end
-
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
-
exec(@strSql)
-
-----------------------------------
-
CREATE PROC sp_PageView
-
@tbname sysname, --要分页显示的表名
-
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
-
@PageCurrent int=1, --要显示的页码
-
@PageSize int=10, --每页的大小(记录数)
-
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
-
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
-
@Where nvarchar(1000)='', --查询条件
-
@PageCount int OUTPUT --总页数
-
AS
-
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 @PageCount=COUNT(*)'
-
+N' FROM '+@tbname
-
+N' '+@Where
-
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
-
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
-
END
-
-
--计算分页显示的TOPN值
-
DECLARE @TopN varchar(20),@TopN1 varchar(20)
-
SELECT @TopN=@PageSize,
-
@TopN1=@PageCurrent*@PageSize
-
-
--第一页直接显示
-
IF @PageCurrent=1
-
EXEC(N'SELECT TOP '+@TopN
-
+N' '+@FieldShow
-
+N' FROM '+@tbname
-
+N' '+@Where
-
+N' '+@FieldOrder)
-
ELSE
-
BEGIN
-
--生成主键(惟一键)处理条件
-
DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)
-
SELECT @Where1=N'',@s=@FieldKey
-
WHILE CHARINDEX(N',',@s)>0
-
SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
-
@Where1=@Where1
-
+N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)
-
+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)
-
SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),
-
@TopN=@TopN1-@PageSize
-
-
--执行查询
-
EXEC(N'SET ROWCOUNT '+@TopN1
-
+N' SELECT '+@FieldKey
-
+N' INTO # FROM '+@tbname
-
+N' '+@Where
-
+N' '+@FieldOrder
-
+N' SET ROWCOUNT '+@TopN
-
+N' DELETE FROM #'
-
+N' SELECT '+@FieldShow
-
+N' FROM '+@tbname
-
+N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1
-
+N') '+@FieldOrder)
-
END
-
-------------------------------------------
-
/*函数名称: GetRecordFromPage
-
-
函数功能: 获取指定页的数据
-
-
参数说明: @tblName 包含数据的表名
-
-
@fldName 关键字段名
-
-
@PageSize 每页记录数
-
-
@PageIndex 要获取的页码
-
-
@OrderType 排序类型, 0 - 升序, 1 - 降序
-
-
@strWhere 查询条件(注意: 不要加where)
-
-
创建时间: 2004-07-04
-
-
修改时间: 2008-02-13
-
-
*/
-
-
Alter PROCEDURE [dbo].[GetRecordFromPage]
-
-
@tblName varchar(255), -- 表名
-
-
@fldName varchar(255), -- 字段名
-
-
@PageSize int = 10, -- 页尺寸
-
-
@PageIndex int = 1, -- 页码
-
-
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
-
-
@IsCount bit = 0, -- 返回记录总数, 非0 值则返回
-
-
@strWhere varchar(2000) = '' -- 查询条件(注意: 不要加where)
-
-
-
-
AS
-
-
-
-
declare @strSQL varchar(6000) -- 主语句
-
-
declare @strTmp varchar(1000) -- 临时变量
-
-
declare @strOrder varchar(500) -- 排序类型
-
-
-
-
if @OrderType != 0
-
-
begin
-
-
set @strTmp = '<(select min'
-
-
set @strOrder = ' order by [' + @fldName + '] desc'
-
-
end
-
-
else
-
-
begin
-
-
set @strTmp = '>(select max'
-
-
set @strOrder = ' order by [' + @fldName +'] asc'
-
-
end
-
-
-
-
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
-
-
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
-
-
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
-
-
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
-
-
+ @strOrder
-
-
-
-
if @strWhere != ''
-
-
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
-
-
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
-
-
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
-
-
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
-
-
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
-
-
-
-
if @PageIndex = 1
-
-
begin
-
-
set @strTmp = ''
-
-
if @strWhere != ''
-
-
set @strTmp = ' where (' + @strWhere + ')'
-
-
-
-
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
-
-
+ @tblName + ']' + @strTmp + ' ' + @strOrder
-
-
end
-
-
if @IsCount != 0
-
-
set @strSQL = 'select count(' + @fldName + ') as Total from [' + @tblName + ']'
-
-
exec (@strSQL)
-
-
go
-
-
-----------------------------
-
-
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/52446787
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）