sqlserver扩展存储过程
什么是存储过程？
q 存储过程（procere）类似于C语言中的函数
q 用来执行管理任务或应用复杂的业务规则
q 存储过程可以带参数，也可以返回结果
q 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
存储过程的优点
(1)执行速度快。
存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。
存储在数据库服务器，性能高。
(2)允许模块化设计。
只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
(3)提高系统安全性。
可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。
存储过程的定义文本可以被加密，使用户不能查看其内容。
(4)减少网络流量：
一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
存储过程的分类
q 系统存储过程
q 由系统定义，存放在master数据库中
q 类似C语言中的系统函数
q 系统存储过程的名称都以“sp_”开头或”xp_”开头
q 用户自定义存储过程
q 由用户在自己的数据库中创建的存储过程
q 类似C语言中的用户自定义函数
常用的系统存储过程
系统存储过程
说明
sp_databases
列出服务器上的所有数据库。
sp_helpdb
报告有关指定数据库或所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象的列表
sp_columns
回某个表列的信息
sp_help
查看某个表的所有信息
sp_helpconstraint
查看某个表的约束
sp_helpindex
查看某个表的索引
sp_stored_proceres
列出当前环境中的所有存储过程。
sp_password
添加或修改登录帐户的密码。
sp_helptext
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
EXEC sp_databases /*列出当前系统中的数据库*/
EXEC sp_renamedb 'Northwind','Northwind1' /*修改数据库的名称(单用户访问, 最简单的办法就是执行SQL语句时关掉企业管理器)*/
USE stuDB
GO
EXEC sp_tables /*当前数据库中查询的对象的列表*/
EXEC sp_columns stuInfo /*返回某个表列的信息*/
EXEC sp_help stuInfo /*查看表stuInfo的信息*/
EXEC sp_helpconstraint stuInfo /*查看表stuInfo的约束*/
EXEC sp_helpindex stuMarks /*查看表stuMarks的索引*/
EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看视图的语句文本*/
EXEC sp_stored_proceres /*查看当前数据库中的存储过程*/
常用的扩展存储过程
q 常用的扩展存储过程：xp_cmdshell
q 可以执行DOS命令下的一些的操作
q 以文本行方式返回任何输出
q 调用语法：
q EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
USE master
GO
EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT /*创建文件夹D:/bank*/
IF EXISTS(SELECT * FROM sysdatabases
WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
(
…
)
GO
EXEC xp_cmdshell 'dir D:/bank/' --查看文件 /*查看文件夹D:/bank*/
如何创建存储过程？
q 定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值,
…… ,
@参数n 数据类型 OUTPUT
AS
SQL语句
GO
q 和C语言的函数一样，参数可选
q 参数分为输入参数、输出参数
q 输入参数允许有默认值
创建不带参数的存储过程
CREATE PROCEDURE proc_stu /* proc_stu为存储过程的名称*/
AS
DECLARE @writtenAvg float,@labAvg float /* 笔试平均分和机试平均分变量 */
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM stuMarks
print '笔试平均分：'+convert(varchar(5),@writtenAvg)
print '机试平均分：'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考试成绩：优秀' /* 显示考试成绩的等级 */
ELSE
print '本班考试成绩：较差'
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员：'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam /* 显示未通过的学员 */
FROM stuInfo INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO
调用存储过程
q EXECUTE（执行）语句用来调用存储过程
q 调用的语法
EXEC 过程名 [参数]
创建带参数的存储过程
q 存储过程的参数分两种：
q 输入参数
q 输出参数
q 输入参数：
用于向存储过程传入值，类似C语言的按值传递;
q 输出参数：
用于在调用存储过程后，
返回结果，类似C语言的
按引用传递;
带输入参数的存储过程
问题：
修改上例：由于每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。
分析：
在述存储过程添加2个输入参数：
@writtenPass 笔试及格线
@labPass 机试及格线
CREATE PROCEDURE proc_stu
@writtenPass int = 60, /*输入参数：笔试及格线*/
@labPass int = 60 /*输入参数：机试及格线*/
AS
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员：'
SELECT stuName,stuInfo.stuNo,writtenExam, /*查询没有通过考试的学员*/
labExam FROM stuInfo
INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 调用带参数的存储过程
假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分
EXEC proc_stu 60,55
--或这样调用：
EXEC proc_stu @labPass=55,@writtenPass=60
带输出参数的存储过程
q 如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了
问题：
修改上例，返回未通过考试的学员人数。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, /*输出（返回）参数：表示没有通过的人数*/
@writtenPass int=60, /*推荐将默认参数放在最后*/
@labPass int=60
AS
……
SELECT stuName,stuInfo.stuNo,writtenExam, /*统计并返回没有通过考试的学员人数*/
labExam FROM stuInfo INNER JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo)
FROM stuMarks WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 调用带输出参数的存储过程
/*---调用存储过程----*/
DECLARE @sum int /*调用时必须带OUTPUT关键字 ，返回结果将存放在变量@sum中*/
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3 /*后续语句引用返回结果*/
print '未通过人数：'+convert(varchar(5),@sum)+ '人,
超过60%,及格分数线还应下调'
ELSE
print '未通过人数：'+convert(varchar(5),@sum)+ '人,
已控制在60%以下，及格分数线适中'
GO
注意：调用时也必须跟随关键字OUTPUT，否则SQL Server将视为输入参数。
处理存储过程中的错误
q 可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户
q RAISERROR 显示用户定义的错误信息时
q 可指定严重级别，
q 设置系统变量@@ERROR
q 记录所发生的错误等
q RAISERROR语句的用法如下：
RAISERROR (msg_id | msg_str,severity,
state WITH option[,...n]])
• msg_id：在sysmessages系统表中指定用户定义错误信息
• msg_str：用户定义的特定信息，最长255个字符
• severity：定义严重性级别。用户可使用的级别为0–18级
• state：表示错误的状态，1至127之间的值
• option：指示是否将错误记录到服务器错误日志中
RAISERROR 语句每个参数的详细讲解，可以查阅SQL帮助!
问题：
完善上例，当用户调用存储过程时，传入的及格线参数不
在0～100之间时，将弹出错误警告，终止存储过程的执行。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --输出参数
@writtenPass int=60, --默认参数放后
@labPass int=60 --默认参数放后
AS
IF (NOT @writtenPass BETWEEN 0 AND 100)
OR (NOT @labPass BETWEEN 0 AND 100)
/*引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值 */
BEGIN
RAISERROR (‘及格线错误，请指定0－100之间的分
数，统计中断退出',16,1)
RETURN ---立即返回，退出存储过程
END
…..其他语句同上例，略
GO
/*---调用存储过程，测试RAISERROR语句----*/
DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT ,604 /*笔试及格线误输入604分*/
SET @t=@@ERROR
print '错误号：'+convert(varchar(5),@t )
IF @t<>0 /*如果执行了RAISERROR，系统全局@@ERROR将不等于0，表示出现错误*/
RETURN --退出批处理，后续语句不再执行
print '--------------------------------------------------'
IF @sum>=3
print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO
好，我们来做个总结：
• 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等
• 存储过程允许带参数，参数分为：
– 输入参数
– 输出参数
其中，输入参数可以有默认值。
• 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值
• 输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字
• RAISERROR语句用来向用户报告错误
2. SQLSERVER启用存储过程
如果禁用：deny execute on [系统扩展存储过程名] to [角色] ；
再启用，就需要再grant一遍，grant execute on [系统扩展存储过程名] to [角色。
grant execute on xp_cmdshell to public
3. sql server 扩展存储过程是怎样的
存储过程的优点
(1)执行速度快。
存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。
存储在数据库服务器，性能高。
(2)允许模块化设计。
只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
(3)提高系统安全性。
可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。
存储过程的定义文本可以被加密，使用户不能查看其内容。
4. sqlserver怎么备份到网络
最近在看联机从书,在看到sp_admpdevice时无意间看到如下内容:
"
[@physicalname =] 'physical_name'
备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。physical_name 的数据类型为 nvarchar(260)，没有默认值，并且不能为 NULL。
当创建远程网络位置上的备份设备时，请确保在其下启动 SQL Server 的名称对远程的计算机有适当的写入能力。
B. 添加网络磁盘备份设备
下面的示例显示一个远程磁盘备份设备。在其下启动 SQL Server 的名称必须对该远程文件拥有权限。
USE master
EXEC sp_admpdevice 'disk', 'networkdevice', '\\servername\sharename\path\filename.ext'
以前曾看到过有人问,怎么样才能将数据备份到网络上,一直没有答案,然后我就试验了一把.
在服务器上建了一个目录,然后确保共享并有权限,如果需要建在共享目录下的目录下,那还得保证用户有权限
当然,SQLSERVER系统启动早于登录,一般用户会在登录中使用本地系统账户,我想在登录到系统之前一直是
administrator用户模拟,因此,我将登录的账户改为了网络用户,因为我公司有域,因此我将域用户设置为本机
的系统管理员,以确保该服务能启动.
接下来更简单了:
sp_admpdevice 'DISK','NetWork','\\server\d$\databak\xuzh.bak'
go
backup database xuzh to network
得到的结果如下:
已处理 96 页，这些页属于数据库 'xuzh' 的文件 'xuzhP01'（位于文件 2 上）。
已处理 16 页，这些页属于数据库 'xuzh' 的文件 'xuzhP02'（位于文件 2 上）。
已处理 16 页，这些页属于数据库 'xuzh' 的文件 'xuzhS01'（位于文件 2 上）。
已处理 8 页，这些页属于数据库 'xuzh' 的文件 'xuzhS02'（位于文件 2 上）。
已处理 1 页，这些页属于数据库 'xuzh' 的文件 'xuzh_Log'（位于文件 2 上）。
BACKUP DATABASE 操作成功地处理了 137 页，花费了 0.516 秒（2.161 MB/秒）。
成功完成网络备份
另外,----------------------
实例说明：
环境：win2k+sqlserver 2K+查询分析器
SQLSERVER服务实例名称:mainserver
需要备份的数据库名称: msdb
本地机器名称（Client端）：david
本地用户:zf 密码：123
本地域名：domain
本地提供备份需求的文件夹:e:\test
第一步: 建立共享文件夹
在程序代码中调用（或者CMD窗口） net share test=e:\test
或者用NetShareAdd这个API
简要说明：
net share : 是WINDOWS内部的网络命令。
作用：建立本地的共享资源，显示当前计算机的共享资源信息。
语法：参见 net share /?
第二步: 建立共享信用关系
master..xp_cmdshell 'net use \\david\test 123 /user:domain\zf'
简要说明：
1：xp_cmdshell ：是SQLSERVER的扩展存储过程。
作用，以操作系统命令行解释器的方式执行给定的命令字符串，
并以文本行方式返回任何输出。
语法：参见SQLSERVER联机帮助
2:net use : 是WINDOWS内部的网络命令。
作用，将计算机与共享资源连接或断开，或者显示关于计算机
连接的信息。该命令还控制持久网络连接。
语法：参见 net use /?
第三步:备份数据库
backup database msdb to disk='\\david\test\msdb.bak'
这个不需要说明吧，语法参见SQLSERVER联机帮助
第四步: 删除共享文件夹
在程序代码中调用（或者CMD窗口） net share test /delete
或者用NetShareDel这个API
结果:
已处理 1376 页，这些页属于数据库 'msdb' 的文件 'MSDBData'（位于文件 1 上）。
已处理 1 页，这些页属于数据库 'msdb' 的文件 'MSDBLog'（位于文件 1 上）。
BACKUP DATABASE 操作成功地处理了 1377 页，花费了 3.653 秒（3.086 MB/秒）。
这样mainserver服务器上的msdb就备份到了david机器的E:\test\msdb.bak文件了，使用起来很简单吧？恢复数据库操作也是一样，只要将第三个步骤的语句改为'restore database msdb from disk='\\david\test\msdb.bak'就可以啦。。你看完了也可以试试呀？！（最简单的测试工具查询分析器+CMD窗口）
备注：xp_cmdshell 这个扩展存储过程只能SA级别的用户调用，而且是SQLSERVER的安全隐患之一，许多DBA都喜欢将其删除或者禁用，所以开发人员使用时要倍加小心哦。
文章中的例子只是简要的说明了应如何利用扩展存储过程实现远程备份与恢复，没有涉及安全以及其他方面的考虑，希望读者在代码中自行完善。
5. 如何禁用sqlserver扩展存储过程 sp_enlist_tsx
在sql server 2000中,访问一个数据库对象的完整路径为:
servername.dbname.username.objectname
对于本地服务器当前数据库的对象，可以采用相对路径
username.objectname
也就是说person是用户名，是表的拥有者
不过在sql server 2005中，路经为：
servername.dbname.schema.objectname
如果是这个版本，那person就是架构名了 。
schema使架构的意思,在SQL Server 2005中，架构表示的是一种名称空间，是为了标识数据库中的对象建立的命名方案，通常使用的还是用户名。例如一个名为mike的用户，可以为他建立一个名为mike的架构，当mike建立表的时候，表的名字（objectname）为table1，则该表的名称可以表示为mike.table1
6. sqlserver存储过程怎么调试
SQL Server存储过程相信大家都接触过，下面就教您如何实现SQL Server存储过程的单步调试，希望可以让您对SQL Server存储过程有更深的认识。
1)调试
在SQL Server的SQL Query Analyzer(查询分析器)中，打开Object Brower(F8)，在左边的列表中选择一数据库，展开Stored Proceres，然后选择要调试的存储过程（或任意一个存储过程），按右键，点击“Debug”，则弹出Debug Procere对话框，显示该存储过程的参数（可在Proceres列表中选择其他要调试的存储过程），为参数输入初始值，点击“Execute”按钮，进入Debug窗口；可使用上面一排按钮或其对应的快捷键对该存储过程进行“执行”“设断点”“取消断点”“单步跟踪”等操作；窗口下方是参数列表，第一个是自定义参数变量列表，在Debug的过程中显示全局变量和局部变量的值的变化，可对参数列表中该存储过程的参数初值进行修改，再重新调试；第二个列表是系统变量，可自行增加，如添加“@@Error”“@@RowCount”，观看其值的变化，其值不能手工修改。第三个列表是CallStack，暂时不知道怎么用。在参数列表中，不能直接把User_Name()、GetDate()等函数直接放到参数列表中跟踪，要把它们赋值予自定义变量进行查看。
大家可连接Pivot的SQL Server进行尝试，使用本机DB系统如果不行，则需要用Administrator登录本机，然后在企业管理器中，在本机的SQL Server Registration上点击右键，点“属性”，选择“Security”，选择“This account”，输入Administrator和密码，确定之。
2)SQL Server存储过程单步调试
具体步骤如下：
1、将服务器【身份验证】属性设置成【混合模式】（window与sql身份验证）
2、在【控制面板】中打开【服务】将【MSSQLSERVER】服务打开【属性】，选择【登录】页面，将登录身份设置成服务器本地帐号和该帐号密码，如administrator，密码123；
3、重新启动sqlserver服务，此时的服务指的是【SQL服务管理器】中的SQL SERVER服务；
假设【帐号】设置为administrator
此时达到的效果是：服务器本地帐号administrator与客户端上的administrator（并且该帐号的密码要与服务器密码相同）可以通过【查询分析器】进行调试；
如果想让【其他帐号】也能够调试，那么还需要如下设置：
1、在【服务器】上运行dcomcnfg.exe；
2、在【默认安全机制】中【默认访问权限】右边点击【编辑默认值】选择允许调试的帐号类型，如users用户类型，sample帐号有包含users组；
3、重新启动sqlserver服务；
3、在客户端上创建与服务帐号密码一样的用户，如sample；
做到这步就可以通过查询分析器的调试功能进行单步调试了
7. 为什么数据库sqlserver中有存储过程这个
存储过程（Stored Procere）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
种类：
1系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
2本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。
3临时存储过程
分为两种存储过程：
一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4远程存储过程
在SQL Server2005中，远程存储过程(Remote Stored Proceres)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
5扩展存储过程
扩展存储过程(Extended Stored Proceres)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
8. 关于sql sever 的系统存储过程
存储过程分为三类：
系统存储过程：（System stored Procere）sp_开头，为SQLSERVER内置存储过程。
扩展存储过程：（Extended stored Procere），也就是外挂程序，用于扩展SQLSERVER的功能，以sp_或者xp_开头，以DLL的形式单独存在。
（系统存储过程和扩展存储过程都是在master数据库中。sp_开头的可是全局的，任何一个数据库都可以直接调用的。）
系统存储过程主要分为以下几类：
目录存储过程，例如：
sp_columns 返回当前环境中可查询的指定表或视图的列信息。
sp_tables 返回当前环境下可查询的对象的列表（任何可出现在 FROM 子句中的对象）。
sp_stored_proceres 返回当前环境中的存储过程列表。
复制类存储过程，例如：
sp_addarticle 创建项目并将其添加到发布中。此存储过程在发布服务器的发布数据库上执行。
安全管理类存储过程，例如：
sp_addrole 在当前数据库创建新的 Microsoft�0�3 SQL Server�6�4 角色。
sp_password 添加或更改 Microsoft�0�3 SQL Server�6�4 登录的密码。
分布式查询存储过程，例如：
sp_foreignkeys 返回引用主键的外键，这些主键在链接服务器中的表上。
sp_primarykeys 返回指定远程表的主键列，每个键列占一行。
扩展存储过程：
xp_sendmail 向指定的收件人发送邮件和查询结果集附件。
xp_startmail 启动 SQL 邮件客户端会话。
xp_cmdshell 以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。授予非管理用户执行xp_cmdshell 的权限。
用户定义的存储过程：（User-defined stored Procere），这个就是用户在具体的数据库中自己定义的，名字最好不要以sp_和xp_开头，防止混乱。
注意事项：
1．在存储过程中，有些建立对象的语句是不可使用的：create default,create trigger,create procere,create view,create rule.
2．在同一数据库中，不同的所有者可以建立相同名称的对象名。例如：a.sample,b.sample,c.sample三个数据表可以同时存在。如果存储过程中未指明对象的所有者（例如存储过程中的语句select * from sample，这句中的sample没有指明所有者），在执行的过程中默认的所有者查找顺序是：相应的存储过程的建立者－>相应数据库的所有者。如果这个查找过程中没有把所有者确定下来，系统就要报错。
（这里我额外插一句：如果需要严密的数据操作，在任何操作中尽量加上所有者，例如leijun.sample）
3．在存储过程名称前边添加＃或者＃＃，所建立的存储过程则是“临时存储过程“（＃是局部临时存储过程，＃＃是全局临时存储过程）。
9. sqlserver里存储过程怎么调用存储过程
sqlserver里调用存储过程的具体操作步骤如下：
1、打开SQL Server Managment管理工具，新建一个表。
10. SqlServer存储过程
create
procere
prCreateSubPlan
as
begin
declare
@id
int,
@intCycle
int,
@planName
varchar(100),
@createTime
smalldatetime,
@cycleTime
int
select
@id
=
min(t_cplan_id)
from
t_cplan
while
(@id
is
not
null)
begin
select
@planName=t_plan_name,
@createTime
=
createTime,
@cycleTime
=
cycleTime
from
t_cplan
where
t_cplan_id=@id
select
@intCycle=
0
while
(@intCycle<@cycleTime)
begin
--
表t_plan
列t_plan_id是IDENTITY
列
insert
t_plan
(t_plan_name,
t_cplan_id,
createTime)
values
(@planName,
@id,
dateadd(day,
@intCycle,
@createTime))
select
@intCycle
=
@intCycle
+
1
end
select
@id
=
min(t_cplan_id)
from
t_cplan
where
t_cplan_id>@id
end
end
go