错误处理

我们应该在我们的系统中包括错误处理操作。我们推荐使用@@ERROR对所有可能会出现问题的语句后进行判断.
根据场景的不同,我们可以从两种方法中选择一种,那就是RAISEERROR和RETURN(只在存储过程中可用).
不要在RAISERROR中使用WITH_LOG,因为这要求SA的权限.
不要使用Sysmessages来为RAISERROR存储错误信息,因为在多数据库服务器的环境下会增加维护成本.
当调用一个存储过程时,一定要对错误进行检查,也就是对Return的信息进行检查.如果存储过程返回一个值,此时它将会重值@@Error为0.
EXEC @rc = ValidateReportEndDate @SourceID, @FiscalPeriodID output
IF @rc <> 0 or @@error <> 0
    RAISERROR (‘LoadSalesFile 50001:  Error calling ValidateReportEndDate Date’),18,127
    RETURN -200
END
 

6.6.1 在存储过程和触发器中使用RAISEERROR

在存储过程和触发器中, RAISERROR 应该按以下格式返回错误信息:
 {name of,proc or trigger generating error}, {error number} : {error message string}
示例:
CustomerListGet 50001: Unable to retrieve.  Invalid channel id.  (proc example)
CustomerINS 50002:  User is not a manager.  Insert denied.    (trigger example)
 
这是一个简单的例子:
IF @@ERROR <> 0
BEGIN
      SELECT @errmsg='50000: Cannot Declare SnapShot_Cursor'
      GOTO ErrorHandler
END  
ErrorHandler:
SELECT @errmsg = @procname+'  '+@errmsg
RAISERROR (@errmsg,18,127)

6.7 Print 语句

在存储过程和SQL脚本中我们使用print 来把某些重要点的信息打印出来,从而帮助我们排错或者是查看实现的性能.在SQL语句完成后打印出对象名字,影响的行数和用时将会很有用.
语句RAISEERROR(‘’,0,1) WITH NOWAIT语句在PRINT 或者SELECT中是首选的,因为它将将消息立即发送给客户端。Print 或者SELECT将会在批处理结束时才送到客户端(或者是缓冲区满).
这就是个例子:
 
   SELECT @Msg = 'Rows inserted from  ' +@SalesTbl +':  ('+
                 + convert(varchar,@rows)
                 +')   at  '+convert(varchar,getdate(),120)
   RAISERROR (@Msg, 0,1) WITH NOWAIT
  
Or
   SELECT @Time = convert(varchar,getdate(),120)
   RAISERROR( '%d rows have been inserted to %s table at %s' ,0,1
                    ,@rows, @obj,@Time) WITH NOWAIT
Output:
34567 rows have been inserted to Sales133 table at 2001-02-21 14:47:26
 

6.8 参照完整性

使用ALTER TABLE ADD CONSTRAINT 命令来强制参照完整性.  除非要调整数据库间的完整性，否则不要使用触发器来强制参照完整性。

6.8.1 主键

每个表都应该有主健存在。
当需要使用代理关键字来标识行的时候，使用identity列.  此时应该在此列上使用primary约束
从SQLServer 7.0开始,我们一般推荐在主键上创建clustered 索引来最小化存储空间和达到性能要求.

6.8.2 外键

在外键的定义中,FK列应该和被参照的PK(Unique)列有相同的数据类型.注意, 在一列上创建FK并不会自动在此列上创建索引.所以我们推荐在FK上手动创建索引以提升性能.

6.9 触发器

触发器可以被用来强制商业规则,在数据库间强制数据完整性.
 

6.10 游标

除非有特殊的原因,定义游标时要定义局部的,只读向前的和静态的.
 
DECLARE ErrorStatsCursor CURSOR LOCAL FORWARD_ONLY STATIC FOR
--    DECLARE ErrorStatsCursor INSENSITIVE CURSOR FOR -- This is the global cursor ANSI92
SELECT   PumpMilestoneID
FROM     PumpMilestone
ORDER BY PumpMilestoneID
  
OPEN ErrorStatsCursor
FETCH NEXT FROM ErrorStatsCursor
INTO @MilestoneID
  
WHILE @@FETCH_STATUS = 0
BEGIN
   -- First, get the average time for the Milestone
 
   ***
  
   SELECT @MsgID = @@Error
   IF @MsgID <> 0
   GOTO ErrorHandler
  
   FETCH NEXT FROM ErrorStatsCursor
   INTO @MilestoneID
END
  
CLOSE ErrorStatsCursor
DEALLOCATE ErrorStatsCursor
  
RETURN 0
  
ErrorHandler:
IF CURSOR_STATUS('local', 'ErrorStatsCursor') >= 0 -- Cursor Open
   CLOSE ErrorStatsCursor
IF CURSOR_STATUS('local', 'ErrorStatsCursor') = -1 -- Cursor Closed
   DEALLOCATE ErrorStatsCursor
  
/*    IF CURSOR_STATUS('global', 'ErrorStatsCursor') >= 0 -- Open and Allocated so close it
  CLOSE ErrorStatsCursor
IF CURSOR_STATUS('global', 'ErrorStatsCursor') = -1 -- Cursor Closed
  DEALLOCATE ErrorStatsCursor
*/
EXEC util_ErrorLog @ProcName, @MsgText, @MsgID
RAISERROR (@MsgText, 18, 127)
RETURN -200

6.11 用户自定义函数

调用时一定要使用所有者后加用户自定义函数名的形势,例如:
SELECT dbo.fnColumnList('sysobjects', 1)
应该避免:
       不能够使用临时表,但是可以使用表变量
       在用户自定义函数中不能够使用debugging,print和RAISEERROR
       不能够输出多个值,除非输出表
       在用户自定义函数中不能够使用可选参数,比如你在一个函数中定义了三个参数,并为它们赋值,以后如果你调用此函数时一定要为这三个参数分别赋值.
 

6.12 局部变量命名标准

这是局部变量的命名标准:
 

@rows	int	标识此语句影响的行数: SELECT @rows = @@rowcount
@err	int	本地变量存储全局变量@@error的值： SELECT @err = @@error
@errmsg	varchar(200)	这个用来存储错误信息. 在 RAISERROR有他的编码示例.
@msg	varchar(200)	被打印出来的message字符串。

 

6.13 返回最后插入的标识值

在@@IDENTITY之外,SQL Server 2000介绍了另外两种方法来对Identiey进行检查.我认为 Scope_identity()比@@IDENTITY要好用一些.因为Scope_IDENTITY()返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值.
Ø    @@IDENTITY: 返回最后插入的标识值。
Ø    Scope_identity():返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。
 
如下:
drop table jbt1
drop table jbt1hist
  
create table jbt1 (a int identity(1,1), b int not null)
create table jbt1hist (a int, b int not null, c int identity(222,1))
  
drop trigger jbt1ins
create trigger jbt1ins on jbt1 for insert as
insert into jbt1hist (a,b) select a,b from jbt1
  
insert into jbt1 (b) select 1
select @@identity -- gives identity of the table in the trigger (jbt1hist).
Select SCOPE_IDENTITY() -- gives identity of table we just inserted into (jbt1)
 
另一个就是IDENT_CURRENT, 返回为任何会话和任何作用域中的指定表最后生成的标识值。
SELECT IDENT_CURRENT('extractlist')