Tag: 存储过程预览模式: 普通 | 列表

Delphi与SQL存储过程

作者:admin 日期:2010-03-16

Delphi代码

存储过程
1．创建存储过程
创建存储过程主要用SQL语句Create PROCEDURE来实现，并用“Exec语句+存储过程名”来执行已创建的存储过程。
本示例是用存储过程来执行多表合并的操作。
操作步骤如下：
（1）运行Delphi，在窗体中添加TADOConnection、TADOQuery、TDataSource、TButton、TEdit和TDBGrid等组件。
（2）主要程序代码如下：
with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.add('create procedure '+Edit1.Text); //创建存储过程
SQl.Add('as ');
SQL.Add('select k.学生编号,k.学生姓名,j.外语 FROM tb_stu AS k INNER JOIN tb_mark AS j ON k.学生编号 = j.学生编号 Where (((k.学生编号)=[j].[学生编号]))');
ExecSQL;
SQL.Clear;
SQL.Add('Exec '+Edit1.Text); //执行存储过程
Open;
end;
2．对存储过程进行加密
在创建存储过程时，可以为存储过程加密，这样用户可以隐藏存储过程的文本，避免其他用户在存储过程属性中查看其语句，主要用WITH ENCRYPTION选项实现。在对存储过程加密前必须对已有的存储过程进行删除。
下面用存储过程的WITH ENCRYPTION选项来创建一个加密存储过程。
操作步骤如下：
（1）运行Delphi，在窗体中添加TADOConnection、TADOQuery、TDataSource、TButton、TEdit、TMemo和TDBGrid等组件。
（2）主要程序代码如下：
with ADOQuery1 do
begin
close;
SQL.Clear;
SQL.Add('if EXISTS(Select name FROM sysobjects Where name='+QuotedStr(Trim(Edit1.Text))+' AND type = ''p'')'); //判断存储过程是否存在
SQL.Add('Drop PROCEDURE '+Trim(Edit1.Text)); //删除已有的存储过程
EXECSQL;
close;
SQL.Clear;
//为存储过程加密
SQL.Add('Create PROCEDURE '+Edit1.Text+' WITH ENCRYPTION AS '+Trim(Memo1.Text));
EXECSQL;
end;
3．用存储过程附加数据库
使用存储过程附加数据库可以方便地将没有连接的数据库附加到当前服务器上，只是在附加数据库前先将存储过程所在的数据库以手动方式附加到服务器上。附加数据库主要用系统存储过程sp_attach_db来实现。在存储过程中不可以用占位符N。
下面用存储过程将服务器以外的数据库附加到服务器上，只适用于英文数据库名。
操作步骤如下：
（1）运行Delphi，在窗体中添加TADOConnection、TADOQuery、TDataSource、TButton和TEdit等组件。
（2）主要程序代码如下：
创建一个含有参数的存储过程。
with ADOQuery1 do
begin
close;
SQL.Clear;
SQL.Add('Create PROCEDURE '+Trim(Edit4.Text));
SQL.Add('@Table varchar(20),');
SQL.Add('@date varchar(200),');
SQL.Add('@LOG varchar(200)');
SQL.Add('AS');
SQL.Add('EXEC sp_attach_db @dbname=@Table,@filename1=@date,@filename2 =@LOG');
EXECSQL;
end
执行带参数的存储过程。
with ADOQuery1 do
begin
close;
SQL.Clear;
SQL.Add('if EXISTS(Select name FROM sysobjects Where name='+QuotedStr(Trim(Edit4.Text))+' AND type = ''p'')'); //判断存储过程是否存在
//以传参的形式调用存储过程
SQL.Add('EXEC '+Trim(Edit4.Text)+' @Table='+QuotedStr(Trim(Edit1.Text))+',@date='+QuotedStr(Trim(Edit2.Text))+ ',@LOG='+QuotedStr(Trim(Edit3.Text)));
EXECSQL;
end;
4．用存储过程备份数据库
备份数据库与附加数据库所用的方法基本相同，只是备份数据库用SQL语句BACKUP DATABASE来实现，而附加数据库则用系统存储过程sp_attach_db来实现。
下面利用存储过程对服务器上的任意数据库进行备份。
操作步骤如下：
（1）运行Delphi，在窗体中添加TADOConnection、TADOQuery、TDataSource、TButton和TEdit等组件。
（2）主要程序代码如下：
创建一个含有参数的存储过程。
with ADOQuery1 do
begin
close;
SQL.Clear;
SQL.Add('Create PROCEDURE '+Trim(Edit3.Text));
SQL.Add('@name varchar(20),');
SQL.Add('@way varchar(200)');
SQL.Add('AS');
SQL.Add('backup database @name to disk=@way');
EXECSQL;
Showmessage('存储过程'+Edit3.Text+'创建成功。');
end
执行存储过程备份数据库。
with ADOQuery1 do
begin
close;
SQL.Clear;
SQL.Add('if EXISTS(Select name FROM sysobjects Where name='+QuotedStr(Trim(Edit3.Text))+' AND type = ''p'')'); //判断存储过程是否存在
//以传参的形式调用存储过程
SQL.Add('EXEC '+Trim(Edit3.Text)+' @name='+Trim(ComboBox1.Text)+',@way='+QuotedStr(Trim(Edit2.Text)));
EXECSQL;
end;

Tags: SQL SQL 存储过程存储过程 Delphi Delphi

分类:Delphi | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 34

查看MSSQLServer数据库空间使用情况的存储过程

作者:admin 日期:2010-03-14

SQL代码

Create procedure SpaceUsed
as
begin
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5) -- Param. for specifying that
create table #temp1
(
表名 varchar(200) null,
行数 char(11) null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
未用空间 varchar(15) null
)
--select @objname='N_dep' -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
declare cur_table cursor for
select name from sysobjects where type='u'
Open cur_table
fetch next from cur_table into @objname
While @@FETCH_STATUS=0
begin
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end
/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
insert into #temp1
select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
Drop table #spt_space
end
fetch next from cur_table into @objname
end
Close cur_table
DEALLOCATE cur_table
Select * from #temp1 order by len(数据使用空间) desc,数据使用空间 desc,保留空间 desc
Drop table #temp1
return (0)
end
GO
详细出处参考：http://www.jb51.net/article/6788.htm

Tags: SQL SQL 存储过程存储过程空间空间

分类:MSSQL | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 74

SQL2000存储过程的基础

作者:admin 日期:2010-03-14

SQL代码

存储过程的概念
SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。
存储过程是SQL语句和可选控制流语句的预编译集合，存储在数据库中，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。
在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。
可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：
可以在单个存储过程中执行一系列SQL语句。
可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。
存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，而且减少网络通信的负担。
安全性更高。
创建存储过程
在SQL Server中，可以使用三种方法创建存储过程：
①使用创建存储过程向导创建存储过程。
②利用SQL Server 企业管理器创建存储过程。
③使用Transact-SQL语句中的Create PROCEDURE命令创建存储过程。
下面介绍使用Transact-SQL语句中的Create PROCEDURE命令创建存储过程
创建存储过程前，应该考虑下列几个事项：
①不能将 Create PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。
②存储过程可以嵌套使用，嵌套的最大深度不能超过32层。
③创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。
④存储过程是数据库对象，其名称必须遵守标识符规则。
⑤只能在当前数据库中创建存储过程。
⑥ 一个存储过程的最大尺寸为128M。
使用Create PROCEDURE创建存储过程的语法形式如下：
QUOTE:
Create PROC[EDURE]procedure_name[;number][;number]
[{@parameter data_type}
[VARYING][=default][OUTPUT]
][,...n] WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement [ ...n ]
用Create PROCEDURE创建存储过程的语法参数的意义如下：
procedure_name：用于指定要创建的存储过程的名称。
number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。
@parameter：过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。
data_type：用于指定参数的数据类型。
VARYING：用于指定作为输出OUTPUT参数支持的结果集。
Default：用于指定参数的默认值。
OUTPUT：表明该参数是一个返回参数。
例如：下面创建一个简单的存储过程productinfo，用于检索产品信息。
USE Northwind
if exists(select name from sysobjects
where name='productinfo' and type = 'p')
drop procedure productinfo
GO
create procedure productinfo
as
select * from products
GO
通过下述sql语句执行该存储过程：execute productinfo
即可检索到产品信息。
执行存储过程
直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：
[[EXEC[UTE]]
{ [@return_status=]
{procedure_name[;number]|@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[ WITH RECOMPILE ]
使用 EXECUTE 命令传递单个参数，它执行 showind 存储过程，以 titles 为参数值。showind 存储过程需要参数 (@tabname)，它是一个表的名称。其程序清单如下：
EXEC showind titles
当然，在执行过程中变量可以显式命名：
EXEC showind @tabname = titles
如果这是 isql 脚本或批处理中第一个语句，则 EXEC 语句可以省略：
showind titles或者showind @tabname = titles
下面的例子使用了默认参数
USE Northwind
GO
Create PROCEDURE insert_Products_1
( @SupplierID_2 int,
@CategoryID_3 int,
@ProductName_1 nvarchar(40)='无')
AS Insert INTO Products
(ProductName,SupplierID,CategoryID)
VALUES
(@ProductName_1,@SupplierID_2,@CategoryID_3)
GO
exec insert_Products_1 1,1
Select * from Products where SupplierID=1 and CategoryID=1
GO
下面的例子使用了返回参数
USE Northwind
GO
Create PROCEDURE query_products
( @SupplierID_1 int,
@ProductName_2 nvarchar(40) output)
AS
select @ProductName_2 = ProductName from products
where SupplierID = @SupplierID_1
执行该存储过程来查询SupplierID为1的产品名：
declare @product nvarchar(40)
exec query_products 1,@product output
select '产品名'= @product
go
查看存储过程
存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。
使用企业管理器查看用户创建的存储过程
在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。
使用系统存储过程来查看用户创建的存储过程
可供使用的系统存储过程及其语法形式如下：
sp_help：用于显示存储过程的参数及其数据类型
sp_help [[@objname=] name]
参数name为要查看的存储过程的名称。
sp_helptext：用于显示存储过程的源代码
sp_helptext [[@objname=] name]
参数name为要查看的存储过程的名称。
sp_depends：用于显示和存储过程相关的数据库对象
sp_depends [@objname=]’object’
参数object为要查看依赖关系的存储过程的名称。
sp_stored_procedures：用于返回当前数据库中的存储过程列表
修改存储过程
存储过程可以根据用户的要求或者基表定义的改变而改变。使用Alter PROCEDURE语句可以更改先前通过执行 Create PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：
AlterPROC[EDURE]procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]][,...n] [WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
sql_statement [ ...n ]
重命名和删除存储过程
1. 重命名存储过程
修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：
sp_rename 原存储过程名称，新存储过程名称
另外，通过企业管理器也可以修改存储过程的名称。
删除存储过程
删除存储过程可以使用Drop命令，Drop命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
drop procedure {procedure} [,…n]
当然，利用企业管理器也可以很方便地删除存储过程。
存储过程的重新编译
在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQL Server提供三种重新编译存储过程的方法：
1、在建立存储过程时设定重新编译
语法格式：Create PROCEDURE procedure_name WITH RECOMPILE AS sql_statement
2、在执行存储过程时设定重编译
语法格式： EXECUTE procedure_name WITH RECOMPILE
3、通过使用系统存储过程设定重编译
语法格式为： EXEC sp_recompile OBJECT
系统存储过程与扩展存储过程
1.系统存储过程
系统存储过程存储在master数据库中，并以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help、显示存储过程和其它对象的文本的存储过程sp_helptext等。
2.扩展存储过程：
扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用Transact-SQL过程那样从Transact-SQL语句调用这些函数。
例: 利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。
执行代码：
use master
exec xp_cmdshell 'dir *.exe'
执行结果返回系统目录下的文件内容文本信息。
最后给大家举一个例子：
QUOTE:
IF exists (select * from SysObjects where name='more_than_total' and type='p')
drop procedure more_than_total
go
Create PROCEDURE More_Than_Total
@total money = 0
AS
Declare @amount smallint
BEGIN
select distinct
P.productName,
S.contactName,
P.UnitPrice
from Products P inner join [order Details] O
on p.productID=o.productID inner join suppliers s
on p.supplierID=s.SupplierID
where O.productID in
(select productID
from [order Details]
group by productId
having sum(quantity*unitprice)>@total
)
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/renzhaoqiang/archive/2009/08/21/4468848.aspx
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiaowei_001/archive/2009/10/09/4645814.aspx

Tags: SQL SQL 存储过程存储过程

分类:MSSQL | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 48

sqlserver 系统存储过程

作者:admin 日期:2010-03-14

SQL代码

sqlserver 系统存储过程这样大家就知道这些存储过程的作用了。
/*存储过程*/
sp_databases --列出服务器上的所有数据库
sp_server_info --列出服务器信息，如字符集，版本和排列顺序
sp_stored_procedures--列出当前环境中的所有存储过程
sp_tables --列出当前环境中所有可以查询的对象
sp_start_job --立即启动自动化任务
sp_stop_job --停止正在执行的自动化任务
sp_password --添加或修改登录帐户的密码
sp_configure --显示(不带选项)或更改(带选项)当前服务器的全局配置设置
sp_help --返回表的列名，数据类型，约束类型等
sp_helptext --显示规则，默认值，未加密的存储过程，用户定义的函数，
--触发器或视图的实际文本
sp_helpfile --查看当前数据库信息
sp_dboption --显示或更改数据库选项
sp_detach_db --分离数据库
sp_attach_db --附加数据库
sp_addumpdevice --添加设备
sp_dropdevice --删除设备
sp_pkeys --查看主键
sp_fkeys --查看外键
sp_helpdb --查看指定数据库相关文件信息
sp_addtype --自建数据类型
sp_droptype --删除自建数据类型
sp_rename --重新命名数据库
sp_executesql --执行SQL语句
sp_addlogin --添加登陆
sp_droplogin --删除登录
sp_grantdbaccess --把用户映射到登录，即添加一个数据库安全帐户并授予塔访问权限
sp_revokedbaccess--撤销用户的数据访问权,即从数据库中删除一个安全帐户
sp_addrole --添加角色
sp_addrolemember --向角色中添加成员，使其成为数据库角色的成员
sp_addsrvrolemember--修改登录使其成为固定服务器角色的成员
sp_grantlogin --允许使用组帐户或系统用户使用Windows身份验证连接到SQL
sp_defaultdb --修改一个登录的默认数据库
sp_helpindex --用于查看表的索引
sp_cursoropen --定义与游标和游标选项相关的SQL语句，然后生成游标
sp_cursorfetch --从游标中提取一行或多行
sp_cursorclose --关闭并释放游标
sp_cursoroption --设置各种游标选项
sp_cursor --用于请求定位更新
sp_cursorprepare --把与游标有关的T-SQL语句或批处理编译成执行计划，但并不创建游标
sp_cursorexecute --从由sp_cursorprepare创建的执行计划中创建并填充游标
sp_cursorunprepare --废弃由sp_cursorprepare生成的执行计划
sp_settriggerorder --指定第一个或最后一个激发的、与表关联的 AFTER 触发器。在第一个
--和最后一个触发器之间激发的 AFTER 触发器将按未定义的顺序执行
详细出处参考：http://www.jb51.net/article/18596.htm

Tags: SQL SQL 存储过程存储过程

分类:MSSQL | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 41

MsSql 存储过程分页代码

作者:admin 日期:2010-03-14

SQL代码

MsSql 存储过程分页代码
--使用说明本代码适用于MsSql2000,对于其它数据库也可用.但没必要
--创建存储过程
Create PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式）
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名，不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX('.',@fldName))
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end
-- 以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
-- 如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
go
--测试
create table news --建表
(
n_id int iDENTITY(1,1) primary key,
n_title char(200),
n_content text
)
--写循环插入1000000条的数据
create proc tt
as
declare @i int
set @i=0
while(@i<1000000)
begin
insert into news(n_title,n_content) values('sb','dsfsdfsd')
set @i=@i+1
end
exec tt
exec pagination 'news','*','n_id',1000,2,0,0,''
自己改写的一个分页存储过程
Create PROC Paging
(
@pageSize int,
@pageIndex int,
@pageField nvarchar(32),
@countTotal bit=1,
@fieldQuery nvarchar(512),
@tableQuery nvarchar(512),
@whereQuery nvarchar(2048),
@orderQuery nvarchar(512)
)
AS
DECLARE @bdate Datetime
SET @bdate = getdate()
DECLARE @itemcount int
SET @itemcount=@pageIndex*@pageSize
DECLARE @itemlowwer int
SET @itemlowwer=(@pageIndex-1)*@pageSize
DECLARE @cmd nvarchar(3062)
IF @pageIndex=1
SET @cmd ='Select TOP ‘+CAST(@pageSize AS NVARCHAR)+' ‘+@fieldQuery+' FROM ‘+@tableQuery+' Where ‘+@whereQuery+' ORDER BY ‘+@orderQuery
ELSE
SET @cmd='Select ‘+@fieldQuery+' FROM ‘+@tableQuery+' Where ‘+@pageField+' IN (Select TOP ‘+CAST(@itemcount as nvarchar)+' ‘+@pageField+' FROM ‘+@tableQuery+' Where ‘+@whereQuery+' ORDER BY ‘+ @orderQuery+')
AND ‘+@pageField+' NOT IN (Select TOP ‘ +CAST(@itemlowwer as nvarchar)+' ‘+@pageField+' FROM ‘+@tableQuery+' Where ‘+@whereQuery+' ORDER BY ‘+ @orderQuery+')'
–print @cmd
EXEC(@cmd)
Select DATEDIFF( ms , @bdate , getdate() )
IF @countTotal =1
BEGIN
SET @cmd = ‘Select COUNT( 0) FROM ‘+@tableQuery+' Where ‘+@whereQuery
EXEC(@cmd)
END
GO