Don't display empty select results-CodePudding

I have a T-SQL script that performs many select statements. Most times these statements don't return anything, so the output shows something like this:

script results.

Is there a way to not show the headers of the select if the result is empty?

CodePudding user response：

You can select into a #temp table and only select from it if you inserted any rows.

SELECT <cols> INTO #a
  FROM dbo.somewhere
  WHERE …;

If @@ROWCOUNT > 0
BEGIN
  SELECT <cols> FROM #a;
END

SELECT <cols> INTO #b
  FROM dbo.somewhere_else
  WHERE …;

If @@ROWCOUNT > 0
BEGIN
  SELECT <cols> FROM #b;
END

…

Or if all the resultsets are identical, you could do:

DECLARE @rc int = 0;

SELECT <cols> INTO #a
  FROM dbo.somewhere
  WHERE …;

SET @rc  = @@ROWCOUNT;

SELECT <cols> INTO #b
  FROM dbo.somewhere_else
  WHERE …;

SET @rc  = @@ROWCOUNT;

...

IF @rc > 0
BEGIN
  SELECT <cols> FROM #a
  UNION ALL
  SELECT <cols> FROM #b
  UNION ALL
  ...;
END

Or even:

SELECT <cols> INTO #x FROM 
(
  SELECT <cols> FROM dbo.somewhere
  UNION ALL
  SELECT <cols> FROM dbo.somewhere_else
  UNION ALL
  ...
) AS x;

IF @@ROWCOUNT > 0
BEGIN
  SELECT <cols> FROM #x;
END

And if you need to know which table a row came from:

SELECT <cols> INTO #x FROM 
(
  SELECT src = 'somewhere', <cols> FROM dbo.somewhere
  UNION ALL
  SELECT src = 'somewhere_else', <cols> FROM dbo.somewhere_else
  ...
) AS x;

IF @@ROWCOUNT > 0
BEGIN
  SELECT src, <cols> FROM #x;
END