SQL Server 2005存储过程开发的一点经验


1、  存储过程

1)  尽量将代码段放到TRY…CATCH…

但凡使用C#写过代码的人,都知道C#TRY…CATCH…的运行和出错跳转逻辑,而SQL Server2005中,其运行和出错跳转逻辑与在C#中是一致的。TRY…CATCH…SQL Server2005中新增的,功能强大,且很好用。

2) 定义变量时,可以使用前缀的方式标识变量的类型。在SQL Server中开发基本和在记事本中开发差不了多少,没有在Visual StudioIDE中那么舒服,还有智能感知提示,所以变量要尽量简单清楚。如要定义一个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 OUTPUTSP1调用SP2时,先定义一个CURSOR类型的变量DECLARE @xxx_CURSOR CURSOR,然后调用SP2 SP2 @xxx_CURSOR OUTPUT 

6) EXECSP_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_RowsCountINT类型的传出参数,第三个参数是用来接收执行完毕后传出参数的结果的。

7) 所有与NULL值运算的结果均为NULL。这个问题困扰了好久,大家要小心。

8) 对某些传入参数的处理,要明确该值如果为NULL时的处理策略:忽略该值还是使用其NULL值。比如我们的一个表有两个字段,姓名和曾用名,曾用名可空。现在要做一个查询,如果在存储过程中,传入了参数是“张三”,“NULL”时,我们是查姓名是“张三”,没有曾用名(曾用名 IS NULL);还是姓名是“张三”,不管其曾用名(忽略NULL)。很明显,策略的确定会影响查询结果。 

 

2、 存储过程与事务

估计写过存储过程的人,最讨厌和烦人的莫过于对事务的控制。如果是简单的begin transactioncommit transationrollback 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 >AND @b_NewTran =1

    BEGIN

       COMMIT TRANSACTION

    END

END TRY

BEGIN CATCH

--回滚事务到保存点

    ROLLBACK TRANSACTION SAVEPOINT_1

       。。。

END CATCH

当然,并不是说这就结束了,万事大吉了。还是遇到了一些问题,由于时间有限,就留待下回分析了。

 

posted @ 2008-10-29 17:06 春风沉醉的晚上 阅读(...) 评论(...) 编辑 收藏