日度归档:2018年7月16日

mssql sqlserver 正确使用@@error


摘要:
下文讲述在sql脚本编写时,我们应注意@@error的相关写法避免出现bug


在sql脚本编写中,我们常常使用@@error判断存储过程是否执行成功
例:

     DECLARE @a int; 
     BEGIN TRAN
      RAISERROR ('抛出异常-模拟错误',16,1);
     SET @a=99;
       IF  @@ERROR  <> 0 
             BEGIN
                 SELECT ERROR_MESSAGE() [错误信息];
                 ROLLBACK TRAN
             END 
        ELSE
             BEGIN
                SELECT  '执行成功'
                COMMIT TRAN
            END

以上语句,当我们模拟错误时,并通过一个@@error 判断错误数,并输出错误信息和回滚事务 ,但脚本返回的却是”执行成功”,此时我们通过仔细的分析脚本,发现在抛出错误后,增加了一句set @a =99 这条赋值命令一切运行正常,导致@@error的结果为0,所以事务得到正确的提交,即在使用@@error进行相关错误判断时,一定要在sql执行后立即进行相关判断。
————————————————————————————-
在sqlserver 2005以后的版本中,微软开放了try catch 的捕捉异常的方法,我们可以在脚本中采用 try catch的方式捕捉异常,可避免@@error写错位置导致的异常,如下:

DECLARE @a int; 
    BEGIN TRY 
     BEGIN TRAN
      RAISERROR ('抛出异常-模拟错误',16,1);
     SET @a=99;
     SELECT  '执行成功'
                COMMIT TRAN
    END TRY
    BEGIN CATCH 
                 SELECT ERROR_MESSAGE() [错误信息];
                 ROLLBACK TRAN
    END  CATCH

采用以上的方法编写脚本时,当try 中的脚本,运行异常时,会立即进入catch中进行相关运行。

注意事项:
@@error 只可判断前一条sql脚本是否执行正确。

mssql sqlserver sql脚本获取异常存储过程的方法分享


摘要:
下文通过sql脚本获取sqlserver数据库中,已经失效的存储过程(
失效的存储过程指:所对应的表、视图、函数、等等所涉及的系统对象已经被删除或不存在而导致的存储过程无法正常使用,我们将此类存储过程称之为“失效存储过程”


实现思路如下所示:
我们根据失效的规则,获取相关的无效存储过程

SELECT
	b.name AS      [存储过程名称],
	a.referenced_entity_name AS [涉及的对象]
    FROM sys.sql_expression_dependencies a
      INNER JOIN sys.procedures b ON  a.referencing_id = b.object_id 
        WHERE a.referenced_id IS NULL                         --涉及的对象不在系统中
	       AND referenced_schema_name IS NOT NULL
	       AND  a.referenced_server_name IS NULL     --剔除涉及的外部链接数据源
	       AND a.referenced_database_name IS NULL  --涉及的为同服务器上的不同数据库    去掉依赖的是同数据服务器上的其他数据库的。
	       AND a.is_caller_dependent !=1                    --依赖的不是存储过程
	       AND a.referenced_entity_name NOT IN (SELECT name FROM  sysobjects) 所涉及的对象非系统对象
               ORDER BY a.name

mssql sqlserver 使用sql脚本获取存储过程最后运行时间


摘要:
下文通过sql脚本的方式,获取存储过程自sql启动后,最后一次运行时间,如下所示:
实验环境:sqlserver 2008 R2


下文中展示了通过sql脚本的方式,获取存储过程最后一次运行时间,通过此方式我们可以检索出sql引擎中,大量未使用的存储过程,然后对其进行相应的维护操作,具体实现思路如下所示:
使用系统的自带视图,获取存储过程的最后执行时间,如下:

 
  SELECT 
	t1.name AS [用户自定义存储过程名称],
	t1.create_date AS [存储过程创建时间],
	t1.modify_date AS [存储过程最后修改时间],
	t2.last_execution_time AS [存储过程最后运行时间],
	t2.execution_count AS [存储过程启动后总运行次数]   FROM sys.procedures t1 
             LEFT JOIN sys.dm_exec_procedure_stats t2 ON t1.object_id = t2.object_id 
             WHERE t1.is_ms_shipped =0 --去掉系统存储过程
              ORDER BY t2.database_id