SQL Server存储过程编程经验技巧(转)

ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ目前正日益成为ＷｉｎｄｏｗＮＴ操作系统上面最为重
　　ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０００的推出，微软的这种数据库服务系统真正的实现了
　　０系列操作系统一统天下的局面，在微软的操作系统上面，没有任何一种数据库系统能与
　　头羊甲骨文公司的看家数据库系统Ｏｒａｃｌｅ在内。不可否认，ＭＳ　ＳＱＬ　Ｓｅｒ
　　在微软自己的操作系统上面，这一点是ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ的致命伤口。但在另
　　促使ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ在自己仅有的“土地”上面将自己的功能发挥到了极至
　　ｗｓＮＴ系列操作系统的各种潜能！作为ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ数据库系统中很重
　　理的使用存储过程，可以有效的提高程序的性能；并且将商业逻辑封装在数据库系统中的
　　软件系统的可维护性，当你的商业逻辑发生了改变的时候，不再需要修改并编译客户端应
　　众多的用户手中，你只需要修改位于 服务器端的实现相应商业逻辑的存储过程即可。合理
　　以最大限度的利用ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ的各种资源。下面我们来共同看看各种编
　　存储过程和使用存储过程的技巧经验！
　下面我们讨论的前提都是您有过一定的ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ存储过程的编写
　　特别注明，则同时适用于ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７．０和ＭＳ　ＳＱＬ　Ｓｅｒｖ
　１、使用ＯＵＴＰＵＴ类型参数的存储过程的技巧
　　一般的存储过程都是直接返回一段记录集给调用者，但是有的时候我们只需要一些存储过
　　可以指定存储过程的ＯＵＴＰＵＴ参数，比如：
　Ｃｒｅａｔｅ　ｐｒｏｃｅｄｕｒｅ　ＧｅｔＮａｍｅ　
　＠ｕｉｄ　ｎｖａｒｃｈａｒ（１），
　＠ｕｓｅｒｎａｍ　ｎｖａｒｃｈａｒ（１０）＝＇＇　ｏｕｔｐｕｔ
　ＡＳ
　ｓｅｔ　＠ｕｓｅｒｎａｍｅ＝＇ｈｏｎｇｃｈａｏ＇
　ＧＯ
　在上面的存储过程当中我们传递进的参数是＠ｕｉｄ，而参数＠ｕｓｅｒｎａｍｅ则
　　这样，存储过程会返回给我们参数＠ｕｓｅｒｎａｍｅ的值是‘ｈｏｎｇｃｈａｏ’。上
　　是，当在ＳＱＬ２０００中的时候，如果你的存储过程只有一个参数，并且这个参数是Ｏ
　　用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误的情况！
　２、在存储过程中的书写注意事项
　这一点在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７．０和ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２００
　　知道是不是微软的遗漏，那就是有些系统的关键字在不同的版本之间有所不同，比如关键
　　ｓｅｌｅｃｔ　＊　ｆｒｏｍ　ｕｓｅｒｓ　ｗｈｅｒｅ　ｌｅｖｅｌ＝１
　　在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７当中的存储过程当中运行没有丝毫的问题，但是到了Ｍ
　　００当中则会出现运行的错误，原因就是在于在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０００中
　　（奇怪的是ＳＱＬ７当中也同样是关键字，却没问题），所以在ＳＱＬ２０００当中，上
　　ｓｅｌｅｃｔ　＊　ｆｒｏｍ　ｕｓｅｒｓ　ｗｈｅｒｅ　［ｌｅｖｅｌ］＝１
　　从上面的例子中我们可以看到，在你编写存储过程的时候，最好在有可能和系统关键字的
　　围起来，以避免在移植过程中出现的运行错误问题。
　３、在存储过程中使用系统存储过程ＳＰ＿Ｅｘｅｃｕｔｅｓｑｌ的注意事项
　我们在编写自己的存储过程的时候，往往在很多的情况下，会使用到系统的存储过程
　　要的注意的是，如果你在这个存储过程的参数（一般是一段ＳＱＬ语句）当中进行了临时
　　用者来说，这个临时Ｔａｂｌｅ是不可见的，也就是说你无法通过临时Ｔａｂｌｅ来在调
　　决的方法是使用全局临时Ｔａｂｌｅ，也就是“＃＃”开头的Ｔａｂｌｅ。
　４、在存储过程中使用临时Ｔａｂｌｅ和游标的注意事项
　如果我们的商业逻辑比较复杂，在存储过程当中，就需要一些媒介作为中转台，这时
　　务必记得在使用完之后，即使删除使用到的临时Ｔａｂｌｅ。
　而在存储过程当中想要依次遍历一个记录集的唯一方法就是使用系统游标，同样要注
　　闭和销毁游标对象释放他用到的资源。并且不在万不得已的情况下，不要随意使用游标，
　　尤其是对于大并发量的情况下，很容易使得系统资源耗尽而崩溃。
　使用临时Ｔａｂｌｅ和游标各有利弊，在使用的过程中要适当的利用即可！
　５、在存储过程中调用外部的ＡｃｔｉｖｅＸ　ＤＬＬ程序
　有些特殊的情况下，我们可能会需要调用外部的ＡｃｔｉｖｅＸ　ＤＬＬ程序，这个
　　程ｓｐ＿ＯＡＣｒｅａｔｅ以及其他的相关系统存储过程，都是以ｓｐ＿ＯＡ开头的存储
　　过程当中调用ＡｃｔｉｖｅＸ　ＤＬＬ的各种方法和属性。比如下面的例子：
　ＤＥＣＬＡＲＥ　＠ｏｂｊｅｃｔ　ｉｎｔ
　ＤＥＣＬＡＲＥ　＠ｈｒ　ｉｎｔ
　ＤＥＣＬＡＲＥ　＠ｐｒｏｐｅｒｔｙ　ｖａｒｃｈａｒ（２５５）
　ＤＥＣＬＡＲＥ　＠ｒｅｔｕｒｎ　ｖａｒｃｈａｒ（２５５）
　ＤＥＣＬＡＲＥ　＠ｓｒｃ　ｖａｒｃｈａｒ（２５５），　＠ｄｅｓｃ　ｖａｒｃ
　－－　建立一个对象（ＳＱＬＤＭＯ．ＳＱＬＳｅｒｖｅｒ）．
　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＣｒｅａｔｅ　＇ＳＱＬＤＭＯ．ＳＱＬＳｅｒ
　　ＯＵＴ
　ＩＦ　＠ｈｒ　＜＞　０
　ＢＥＧＩＮ
　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓ
　　ＯＵＴ　
　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ
　　ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ
　　ＲＥＴＵＲＮ
　ＥＮＤ
　－－　设置对象的属性．
　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＳｅｔＰｒｏｐｅｒｔｙ　＠ｏｂｊｅｃｔ，　
　　ｉｚｍｏ＇
　ＩＦ　＠ｈｒ　＜＞　０
　ＢＥＧＩＮ
　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓ
　　ＯＵＴ　
　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ
　　ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ
　　ＲＥＴＵＲＮ
　ＥＮＤ
　－－　通过ＯＵＴＰＵＴ参数获取对象的属性值．
　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＧｅｔＰｒｏｐｅｒｔｙ　＠ｏｂｊｅｃｔ，　
　　ｒｏｐｅｒｔｙ　ＯＵＴ
　ＩＦ　＠ｈｒ　＜＞　０
　ＢＥＧＩＮ
　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓ
　　ＯＵＴ　
　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ
　　ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ
　　ＲＥＴＵＲＮ
　ＥＮＤ
　ＰＲＩＮＴ　＠ｐｒｏｐｅｒｔｙ
　－－　调用对象的方法
　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＭｅｔｈｏｄ　＠ｏｂｊｅｃｔ，　＇Ｃｏｎｎ
　　ｙ＿ｓｅｒｖｅｒ＇，　＇ｍｙ＿ｌｏｇｉｎ＇，　＇ｍｙ＿ｐａｓｓｗｏｒｄ＇
　ＩＦ　＠ｈｒ　＜＞　０
　ＢＥＧＩＮ
　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓ
　　ＯＵＴ　
　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ
　　ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ
　　ＲＥＴＵＲＮ
　ＥＮＤ
　－－　销毁已经创建的ＡｃｔｉｖｅＸ对象
　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＤｅｓｔｒｏｙ　＠ｏｂｊｅｃｔ
　ＩＦ　＠ｈｒ　＜＞　０
　ＢＥＧＩＮ
　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓ
　　ＯＵＴ　
　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ
　　ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ
　　ＲＥＴＵＲＮ
　ＥＮＤ
　６、在存储过程中使用数据库事务处理
　在很多的情况下，我们在存储过程中都会遇到需要同时操作多个表的情况，这时候就
　　外而造成的数据的不一致性。这时候就需要将操作多个表的操作放入到事务中进行处理。
　但是需要注意的是，不能在事务中使用return语句强行退出，这样会引发事务的非正
　　性。
　并且，一旦将多个处理放入事务当中，系统的处理速度会有所降低，所以应当将频繁
　　入到多个存储过程当中，这样会大大提高系统的响应速度，但是前提是不违背数据的一致
　看完了上面的这些编写SQL Server存储过程当中的技巧，相信对您或多或少会有些帮
　　总结，可以使得您在应用SQL Server存储过程的时候，有意识的可以避免一些弯路。