如果表为空，如何返回失败

发布于 2024-08-03 14:15:12 字数 206 浏览 5 评论 0原文

使用SQL Server 2005，但仍然使用DTS。我需要添加一个步骤来检查表是否为空，如果是，则以某种方式使该步骤失败。检查表是否为空很容易：

Select count(*) from source_table

但返回 0 也是成功的。如果它是 0，我想要失败（这样我就可以分叉到不同的选项，给我们发电子邮件，跳过一些步骤）。

原文

Using SQL Server 2005, but still using DTS. I need to add a step to check if a table is empty, and somehow fail the step if it is. It's easy checking if the table is empty:

Select count(*) from source_table

But returning 0 is a success, too. If it is 0, I want a failure (so that I can fork to a different option, email us, skip some steps).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

欲拥i 2024-08-10 14:15:12

if (select count(*) from [Table]) = 0 print 'Empty'

if (select count(*) from [Table]) = 0 print 'Empty'

回复收藏 0 原文

萌能量女王 2024-08-10 14:15:12

返回-1怎么样？

Select 
   case when count(*)>=0 then count(*) else -1 end 
from 
   source_table

如果您确实想引发错误，可以使用 RAISERROR 来就这样做：

Declare @RowCount as bigint
set @RowCount=count(*) from source_table
if RowCount =0
RAISERROR ('error message', 50000, 1)  with log

what about returning -1 ?

Select 
   case when count(*)>=0 then count(*) else -1 end 
from 
   source_table

If you really want to raise an error, you can use RAISERROR to do just that :

Declare @RowCount as bigint
set @RowCount=count(*) from source_table
if RowCount =0
RAISERROR ('error message', 50000, 1)  with log

回复收藏 0 原文

情未る 2024-08-10 14:15:12

您可以除以计数结果：

Select 1/count(*) from source_table

You can divide by count result:

Select 1/count(*) from source_table

回复收藏 0 原文

剧终人散尽 2024-08-10 14:15:12

我没有使用DTS。它在 SSIS 中得到了解决，尽管回想起来，我可能可以在 DTS 中做类似的事情。

步骤 1：数据流任务选择计数并将该计数保存到变量中。选择计数需要一些工作：

select cast(count(*) as integer) as Row_Count from MyTable

然后数据流任务的输出是一个脚本组件，它是目标，并且有一个输入列作为 ROW_COUNT，我的 ReadWriteVariables 作为 TableCount（在步骤 1 中用作输入的变量） 2)

步骤 2：评估该计数的脚本任务，如果计数为 0，则失败，否则成功。由此分叉，有成功路线，也有失败路线。

I didn't use DTS. It was resolved in SSIS, although, looking back, I could have probably done something similar in DTS.

Step 1: A data flow task that selects count and saves that count to a variable. The select count took a bit of work:

select cast(count(*) as integer) as Row_Count from MyTable

and then the output of the data flow task was a script component that was a destination and had an input column as that ROW_COUNT, and my ReadWriteVariables as TableCount (the variable that was used as input in step 2)

Step 2: A script task that evaluates that count and fails if the count was 0, succeeds otherwise. Forking from this is a success route and a failure route.

回复收藏 0 原文

~没有更多了~