How to return a failure if a table is empty

Question

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).

Froude

Answer 1

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.

Froude

If you're going to give a downvote, especially on such an old question, it would be nice to give a comment as to why. – Froude

Answer 2

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

Streetlight

That isn't raising an error, and I don't think printing is normally what is needed in DTS or SSIS processing. – Froude

@Froude : I thought you are capable of changing print with raiserror(), sorry :) – Streetlight

Answer 3

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

Lenny

How do you use RAISEERROR in a DTS Execute SQL Task? – Froude

A potentially better way to check for an empty table: if not exists (select top 1 0 from source_table) raiserror(...) – Stelle

Answer 4

You can divide by count result:

Select 1/count(*) from source_table

Undergraduate

I'm a couple of jobs past that issue, so have no way of testing, but this does seem like it could be a good solution. – Froude