MSSQLServer目前正日益成为WindowNT操作系统上面最为重要的一种数据库管理系统随着
MSSQLServer2000的推出微软的这种数据库服务系统真正的实现了在WindowsNT/200
0系列操作系统一统天下的局面在微软的操作系统上面没有任何一种数据库系统能与之相抗衡包括数据库领域的领
头羊甲骨文公司的看家数据库系统Oracle在内不可否认MSSQLServer最大的缺陷就是只能运行
在微软自己的操作系统上面这一点是MSSQLServer的致命伤口但在另一方面却也成了最好的促进剂
促使MSSQLServer在自己仅有的土地上面将自己的功能发挥到了极至最大限度的利用了Windo
wsNT系列操作系统的各种潜能!作为MSSQLServer数据库系统中很重要的一个概念就是存储过程合
理的使用存储过程可以有效的提高程序的性能并且将商业逻辑封装在数据库系统中的存储过程中可以大大提高整个
软件系统的可维护性当你的商业逻辑发生了改变的时候不再需要修改并编译客户端应用程序以及重新分发他们到为数
众多的用户手中你只需要修改位于服务器端的实现相应商业逻辑的存储过程即可合理的编写自己需要的存储过程可
以最大限度的利用MSSQLServer的各种资源下面我们来共同看看各种编写MSSQLServer
存储过程和使用存储过程的技巧经验!
下面我们讨论的前提都是您有过一定的MSSQLServer存储过程的编写经验下面的各种技巧如果没有
特别注明则同时适用于MSSQLServer7.0和MSSQLServer2000
1使用OUTPUT类型参数的存储过程的技巧
一般的存储过程都是直接返回一段记录集给调用者但是有的时候我们只需要一些存储过程返回的一些参数的值这时候
可以指定存储过程的OUTPUT参数比如
CreateprocedureGetName
@uidnvarchar(1)
@usernamnvarchar(10)=''output
AS
set@username='hongchao'
GO
在上面的存储过程当中我们传递进的参数是@uid而参数@username则在调有的时候不需要传递进去
这样存储过程会返回给我们参数@username的值是hongchao上面的都比较的容易需要注意的
是当在SQL2000中的时候如果你的存储过程只有一个参数并且这个参数是OUTPUT类型的你必须在调
用这个存储过程的时候给这个参数一个初始的值否则会出现调用错误的情况!
2在存储过程中的书写注意事项
这一点在MSSQLServer7.0和MSSQLServer2000种有些地方是不一样的也不
知道是不是微软的遗漏那就是有些系统的关键字在不同的版本之间有所不同比如关键字level同样的一句话
select*fromuserswherelevel=1
在MSSQLServer7当中的存储过程当中运行没有丝毫的问题但是到了MSSQLServer20
00当中则会出现运行的错误原因就是在于在MSSQLServer2000中level被当作了关键字
(奇怪的是SQL7当中也同样是关键字却没问题)所以在SQL2000当中上面的语句应当改为
select*fromuserswhere[level]=1
从上面的例子中我们可以看到在你编写存储过程的时候最好在有可能和系统关键字的地方使用[和]将他包
围起来以避免在移植过程中出现的运行错误问题
3在存储过程中使用系统存储过程SP_Executesql的注意事项
我们在编写自己的存储过程的时候往往在很多的情况下会使用到系统的存储过程SP_Execute但是需
要的注意的是如果你在这个存储过程的参数(一般是一段SQL语句)当中进行了临时Table的操作那末对于调
用者来说这个临时Table是不可见的也就是说你无法通过临时Table来在调用者和被调用者之间传递值解
决的方法是使用全局临时Table也就是##开头的Table
4在存储过程中使用临时Table和游标的注意事项
如果我们的商业逻辑比较复杂在存储过程当中就需要一些媒介作为中转台这时候临时表舅发挥了作用但是请
务必记得在使用完之后即使删除使用到的临时Table
而在存储过程当中想要依次遍历一个记录集的唯一方法就是使用系统游标同样要注意的是在使用完成之后及时关
闭和销毁游标对象释放他用到的资源并且不在万不得已的情况下不要随意使用游标因为他会占用较多的系统资源
尤其是对于大并发量的情况下很容易使得系统资源耗尽而崩溃
使用临时Table和游标各有利弊在使用的过程中要适当的利用即可!
5在存储过程中调用外部的ActiveXDLL程序
有些特殊的情况下我们可能会需要调用外部的ActiveXDLL程序这个时候就需要使用到系统的存储过
程sp_OACreate以及其他的相关系统存储过程都是以sp_OA开头的存储过程可以自由的在自己的存储
过程当中调用ActiveXDLL的各种方法和属性比如下面的例子
DECLARE@objectint
DECLARE@hrint
DECLARE@propertyvarchar(255)
DECLARE@returnvarchar(255)
DECLARE@srcvarchar(255)@descvarchar(255)
--建立一个对象(SQLDMO.SQLServer).
EXEC@hr=sp_OACreate'SQLDMO.SQLServer'@object
OUT
IF@hr<>0
BEGIN
EXECsp_OAGetErrorInfo@object@srcOUT@desc
OUT
SELECThr=convert(varbinary(4)@hr)Source=@sr
cDescription=@desc
RETURN
END
--设置对象的属性.
EXEC@hr=sp_OASetProperty@object'HostName''G
izmo'
IF@hr<>0
BEGIN
EXECsp_OAGetErrorInfo@object@srcOUT@desc
OUT
SELECThr=convert(varbinary(4)@hr)Source=@sr
cDescription=@desc
RETURN
END
--通过OUTPUT参数获取对象的属性值.
EXEC@hr=sp_OAGetProperty@object'HostName'@p
ropertyOUT
IF@hr<>0
BEGIN
EXECsp_OAGetErrorInfo@object@srcOUT@desc
OUT
SELECThr=convert(varbinary(4)@hr)Source=@sr
cDescription=@desc
RETURN
END
PRINT@property
--调用对象的方法
EXEC@hr=sp_OAMethod@object'Connect'NULL'm
y_server''my_login''my_password'
IF@hr<>0
BEGIN
EXECsp_OAGetErrorInfo@object@srcOUT@desc
OUT
SELECThr=convert(varb