SQL-Server、TSQL、TRY-CATCH 块

2021-09-10 00:00:00 sql tsql sql-server

我在处理 try/catch 错误时遇到了问题.让我们看看我的(简单)代码:

I'm having a problem with try/catch error-handling. Let's have a look on my (simple) code:

BEGIN TRY
print 'important'
use myDB1; -- no problem, the myDB1 is in place...
select * from dbo.Tab1;
use myDB2;
--here error, the myDB2 is not there, 
--but error handling doesn't jump into catch-block

select * from dbo.Tab2;
END TRY
BEGIN CATCH
    print 'myDB2 is not there'
END CATCH

我知道，我可以说:

select * from myDB2.dbo.Tab2 无需更改为 myDB2，但是当我需要检查(例如..)一个表是否具有标识时

select * from myDB2.dbo.Tab2 without changing to myDB2, but when I need to check (for example..) if a table has an identity 

(((SELECT OBJECTPROPERTY( OBJECT_ID('myDB2.dbo.'+ @TableName), 'TableHasIdentity'))= 1)

我必须从 myDB2 运行它，否则我会得到错误的结果.那么我怎样才能在 catch-block 中捕获错误呢?

I must run this from myDB2, otherwise I'll get a wrong result. So how can I catch the error in the catch-block?

感谢您的帮助

珀克洛特

推荐答案

您需要将测试条件封装在 EXEC 中才能将错误视为运行时问题.然后，您需要完全限定访问可能不存在的数据库的查询的对象，以便您可以避免使用 USE 语句.对于需要本地上下文的 OBJECTPROPERTY 等函数，您可以使用 sp_executesql 在不同的数据库上下文中运行查询并返回可用结果.

You need to encapsulate the test condition in an EXEC to get the error to be treated as a run-time issue. You then need to fully-qualify the objects for the queries that hit databases that might not exist so that you can avoid the USE statement. For functions such as OBJECTPROPERTY that require local context, you can use sp_executesql to run queries in a different database context and return a usable result.

DECLARE @TableName SYSNAME,
        @SQL NVARCHAR(MAX),
        @Result BIT

BEGIN TRY

    USE [master];
    SELECT TOP 1 * FROM sys.objects

    SET @TableName = N'sysjobhistory'
    SET @Result = 0
    SET @SQL = N'USE [msdb]; DECLARE @Result BIT;
                 SET @TempResult = OBJECTPROPERTY( OBJECT_ID(N''' + @TableName +
                 N'''), ''TableHasIdentity'')'

    EXEC sp_executesql @SQL,
                       N'@TempResult BIT OUTPUT',
                       @TempResult = @Result OUTPUT

    SELECT @Result AS [ResultThatCanBeUsedLocally]

    EXEC('USE [NotHere];')

    SELECT TOP 1 * FROM NotHere.sys.objects

END TRY
BEGIN CATCH

    PRINT 'Error!!'
    PRINT ERROR_MESSAGE()

END CATCH

相关文章