admin管理员组文章数量:1032698
sqlserver存储过程
1、 存储过程
1） 尽量将代码段放到TRY…CATCH…。
但凡使用C#写过代码的人，都知道C#中TRY…CATCH…的运行和出错跳转逻辑，而SQL Server2005中，其运行和出错跳转逻辑与在C#中是一致的。TRY…CATCH…是SQL Server2005中新增的，功能强大，且很好用。
2） 定义变量时，可以使用前缀的方式标识变量的类型。在SQL Server中开发基本和在记事本中开发差不了多少，没有在Visual Studio的IDE中那么舒服，还有智能感知提示，所以变量要尽量简单清楚。如要定义一个bit型和一个int型的变量，可以按照以下的方式添加前缀。DECLARE @b_NewTran bit; DECLARE @n_ErrorLogID int;
3） out型的参数一上来就应该初始化。
4） 当然，注释是必不可少的，而且越详细越好。
5） 尽量避免使用游标。据说游标的效率较差。在定义游标时，要检查这个游标（如ORDERLINE_CURSOR）是否已经存在，可以使用如下代码。
IF CURSOR_STATUS('global',N'ORDERLINE_CURSOR')<>-3
BEGIN
DEALLOCATE ORDERLINE_CURSOR
END
如果一个游标已经存在，则应先将其释放。然后再定义。
DECLARE ORDERLINE_CURSOR CURSOR FOR...
游标用完后，应及时关闭，并释放。
CLOSE ORDERLINE_CURSOR
DEALLOCATE ORDERLINE_CURSOR
游标可以作为参数传递，如SP1调用SP2，并从SP2中返回一个游标类型的参数。在SP2中的参数应定义 为 @xxx_CURSORCURSOR VARYING OUTPUT，SP1调用SP2时，先定义一个CURSOR类型的变量DECLARE @xxx_CURSOR CURSOR，然后调用SP2 SP2 @xxx_CURSOR OUTPUT
6） EXEC与SP_EXECUTESQL的用法。他们都可以用于执行一个动态SQL语句。但是对于动态SQL语句中包含了返回值的情况，只有用SP_EXECUTESQL了。比如执行一个动态条件的记录行数查询，先构造SQL语句：SET @c_Sql = 'SELECT @n_RowsCount =COUNT(1) FROM '+ @c_TableName。其中@n_RowsCount是一个变量，我希望通过它取得记录行数，然后调用SP_EXECUTESQL执行语句：EXEC SP_EXECUTESQL @c_Sql,N'@n_RowsCount INT OUTPUT',@n_RowsCount OUTPUT;需要注意的是，SP_EXECUTESQL仍然需要EXEC的配合，因为前者是个存储过程。简单分析一下：@c_Sql是要执行的语句，第二个参数N'@n_RowsCount INT OUTPUT'是指明在这个语句中有一个变量@n_RowsCount是INT类型的传出参数，第三个参数是用来接收执行完毕后传出参数的结果的。
7） 所有与NULL值运算的结果均为NULL。这个问题困扰了好久，大家要小心。
8） 对某些传入参数的处理，要明确该值如果为NULL时的处理策略：忽略该值还是使用其NULL值。比如我们的一个表有两个字段，姓名和曾用名，曾用名可空。现在要做一个查询，如果在存储过程中，传入了参数是“张三”，“NULL”时，我们是查姓名是“张三”，没有曾用名（曾用名 IS NULL）；还是姓名是“张三”，不管其曾用名（忽略NULL）。很明显，策略的确定会影响查询结果。
2、 存储过程与事务
估计写过存储过程的人，最讨厌和烦人的莫过于对事务的控制。如果是简单的begin transaction，commit transation，rollback transaction，在存储过程互相调用，并在其中某处出错的时候，可能会发生诸如“无法回滚当前事务。上一计数器=1，当前计数器=0”之类的事务错误。原因就是对事物的理解不透彻，对SQL Server2005中处理事务不熟知。经过一些研究，我采用了一种简单的自我管理的方式来进行事务控制。
DECLARE @b_NewTran bit --本SP是否开启了子事务:1，是；，否
BEGIN TRY
--先判断是否已经有了事务。有，则设置一个保存点；没有，则新启一个事务
IF @@TRANCOUNT >0
BEGIN
SAVE TRANSACTION SAVEPOINT_1
SET @b_NewTran =0
END
ELSE
BEGIN
BEGIN TRANSACTION SAVEPOINT_1
SET @b_NewTran =1
END
。。。
。。。
--如果有未提交的事务，且是本过程新开启的，则在过程结束前提交
IF @@TRANCOUNT >0 AND @b_NewTran =1
BEGIN
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
--回滚事务到保存点
ROLLBACK TRANSACTION SAVEPOINT_1
。。。
END CATCH
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2012-03-20，如有侵权请联系 cloudcommunity@tencent 删除开发事务cursor变量存储过程sqlserver存储过程
1、 存储过程
1） 尽量将代码段放到TRY…CATCH…。
但凡使用C#写过代码的人，都知道C#中TRY…CATCH…的运行和出错跳转逻辑，而SQL Server2005中，其运行和出错跳转逻辑与在C#中是一致的。TRY…CATCH…是SQL Server2005中新增的，功能强大，且很好用。
2） 定义变量时，可以使用前缀的方式标识变量的类型。在SQL Server中开发基本和在记事本中开发差不了多少，没有在Visual Studio的IDE中那么舒服，还有智能感知提示，所以变量要尽量简单清楚。如要定义一个bit型和一个int型的变量，可以按照以下的方式添加前缀。DECLARE @b_NewTran bit; DECLARE @n_ErrorLogID int;
3） out型的参数一上来就应该初始化。
4） 当然，注释是必不可少的，而且越详细越好。
5） 尽量避免使用游标。据说游标的效率较差。在定义游标时，要检查这个游标（如ORDERLINE_CURSOR）是否已经存在，可以使用如下代码。
IF CURSOR_STATUS('global',N'ORDERLINE_CURSOR')<>-3
BEGIN
DEALLOCATE ORDERLINE_CURSOR
END
如果一个游标已经存在，则应先将其释放。然后再定义。
DECLARE ORDERLINE_CURSOR CURSOR FOR...
游标用完后，应及时关闭，并释放。
CLOSE ORDERLINE_CURSOR
DEALLOCATE ORDERLINE_CURSOR
游标可以作为参数传递，如SP1调用SP2，并从SP2中返回一个游标类型的参数。在SP2中的参数应定义 为 @xxx_CURSORCURSOR VARYING OUTPUT，SP1调用SP2时，先定义一个CURSOR类型的变量DECLARE @xxx_CURSOR CURSOR，然后调用SP2 SP2 @xxx_CURSOR OUTPUT
6） EXEC与SP_EXECUTESQL的用法。他们都可以用于执行一个动态SQL语句。但是对于动态SQL语句中包含了返回值的情况，只有用SP_EXECUTESQL了。比如执行一个动态条件的记录行数查询，先构造SQL语句：SET @c_Sql = 'SELECT @n_RowsCount =COUNT(1) FROM '+ @c_TableName。其中@n_RowsCount是一个变量，我希望通过它取得记录行数，然后调用SP_EXECUTESQL执行语句：EXEC SP_EXECUTESQL @c_Sql,N'@n_RowsCount INT OUTPUT',@n_RowsCount OUTPUT;需要注意的是，SP_EXECUTESQL仍然需要EXEC的配合，因为前者是个存储过程。简单分析一下：@c_Sql是要执行的语句，第二个参数N'@n_RowsCount INT OUTPUT'是指明在这个语句中有一个变量@n_RowsCount是INT类型的传出参数，第三个参数是用来接收执行完毕后传出参数的结果的。
7） 所有与NULL值运算的结果均为NULL。这个问题困扰了好久，大家要小心。
8） 对某些传入参数的处理，要明确该值如果为NULL时的处理策略：忽略该值还是使用其NULL值。比如我们的一个表有两个字段，姓名和曾用名，曾用名可空。现在要做一个查询，如果在存储过程中，传入了参数是“张三”，“NULL”时，我们是查姓名是“张三”，没有曾用名（曾用名 IS NULL）；还是姓名是“张三”，不管其曾用名（忽略NULL）。很明显，策略的确定会影响查询结果。
2、 存储过程与事务
估计写过存储过程的人，最讨厌和烦人的莫过于对事务的控制。如果是简单的begin transaction，commit transation，rollback transaction，在存储过程互相调用，并在其中某处出错的时候，可能会发生诸如“无法回滚当前事务。上一计数器=1，当前计数器=0”之类的事务错误。原因就是对事物的理解不透彻，对SQL Server2005中处理事务不熟知。经过一些研究，我采用了一种简单的自我管理的方式来进行事务控制。
DECLARE @b_NewTran bit --本SP是否开启了子事务:1，是；，否
BEGIN TRY
--先判断是否已经有了事务。有，则设置一个保存点；没有，则新启一个事务
IF @@TRANCOUNT >0
BEGIN
SAVE TRANSACTION SAVEPOINT_1
SET @b_NewTran =0
END
ELSE
BEGIN
BEGIN TRANSACTION SAVEPOINT_1
SET @b_NewTran =1
END
。。。
。。。
--如果有未提交的事务，且是本过程新开启的，则在过程结束前提交
IF @@TRANCOUNT >0 AND @b_NewTran =1
BEGIN
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
--回滚事务到保存点
ROLLBACK TRANSACTION SAVEPOINT_1
。。。
END CATCH
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2012-03-20，如有侵权请联系 cloudcommunity@tencent 删除开发事务cursor变量存储过程本文标签： sqlserver存储过程
版权声明：本文标题：sqlserver存储过程 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1747962913a2234329.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论