Question

i want to handle error if any table is deleted from the database situation is like -

ALTER procedure ABC as 
begin tran tansinsert
insert into table1 values (1,2,3)
if @@error <> 0
begin
rollback tran tansinsert
end
else
begin
commit tran tansinsert
end

如果我正在运行此程序, 表格表1 不在数据库中, 将如何获取错误消息

Answer 1

单行

IF NOT  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N [dbo].[table1 ] ) AND type in (N U ))
BEGIN
       RAISERROR(......)
END
ELSE
BEGIN
--rest of flow
END
GO

Answer 2

你可以试试这个...

IF OBJECT_ID( DBNAME..TABLENAME ) IS NOT NULL
BEGIN
//YOUR CODE FLOW HERE
END

ELSE
BEGIN
//RAISE ERROR HERE
END
GO

Answer 3

捕获错误的样本代码

BEGIN TRY
     EXECUTE Abc
END TRY

BEGIN CATCH
     print  Error got 
     SELECT 
          ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage;
END CATCH;

若您做了以下操作,这不会有效。

CREATE PROC test
AS
BEGIN TRY
  SELECT * FROM NonexistentTable
END TRY

BEGIN CATCH
   -- some code
END CATCH

唯一可行的方法是如果有一个存储程序, 叫做另一个存储程序, 这样的程序 :

CREATE PROC Test
AS
SELECT * FROM NonexistentTable
GO

CREATE PROC test2
AS
BEGIN TRY
  EXECUTE Test
END TRY

BEGIN CATCH
   -- some code
END CATCH
GO

TRY...CATCH的构造没有困住以下条件:

严重程度为10或10以下的警告或信息信息。
严重性为 20 或 20 以上的错误, 阻止 SQL 服务器数据库引擎任务处理会话。如果发生严重性为 20 或 20 以上的错误, 数据库连接没有中断, TRY...CATCH 将处理错误。
注意问题,如客户间断请求或客户断绝联系。
当使用死亡说明由系统管理员结束会话时。

下列类型的错误在与TRY.CATCH建造的相同执行水平发生时,不由CATCH区块处理:

Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

友情链接