如果没有找到结果，则在查询结果中添加空行 [英] Add empty row to query results if no results found

问题描述

我正在编写由遗留系统调用的存储过程.遗留系统的约束之一是从存储过程返回的单个结果集中必须至少有一行.标准是在第一列中返回零(是的，我知道！).

I'm writing stored procs that are being called by a legacy system. One of the constraints of the legacy system is that there must be at least one row in the single result set returned from the stored proc. The standard is to return a zero in the first column (yes, I know!).

实现这一目标的显而易见的方法是创建一个临时表，将结果放入其中，测试临时表中的任何行，然后返回临时表中的结果或单个空结果.

The obvious way to achieve this is create a temp table, put the results into it, test for any rows in the temp table and either return the results from the temp table or the single empty result.

另一种方法可能是在执行主查询之前对主查询中的同一个 where 子句执行 EXISTS.

Another way might be to do an EXISTS against the same where clause that's in the main query before the main query is executed.

这些都不是很令人满意.谁能想到更好的办法.我在想像这样的 UNION 台词(我知道这行不通):

Neither of these are very satisfying. Can anyone think of a better way. I was thinking down the lines of a UNION kind of like this (I'm aware this doesn't work):

--create table #test
--(
--  id int identity,
--  category varchar(10)
--)
--go
--insert #test values ('A')
--insert #test values ('B')
--insert #test values ('C')

declare @category varchar(10)

set @category = 'D'

select
    id, category
from #test
where category = @category
union
select
    0, ''
from #test
where @@rowcount = 0

推荐答案

这是一个老问题，但我遇到了同样的问题.解决方案非常简单，无需双选:

It's an old question, but i had the same problem. Solution is really simple WITHOUT double select:

select top(1) WITH TIES * FROM (
select
id, category, 1 as orderdummy
from #test
where category = @category
union select 0, '', 2) ORDER BY orderdummy

通过WITH TIES"，您将获得所有行(所有行都有一个 1 作为orderdummy"，因此都是平局)，或者如果没有结果，您将获得默认行.

by the "WITH TIES" you get ALL rows (all have a 1 as "orderdummy", so all are ties), or if there is no result, you get your defaultrow.

这篇关于如果没有找到结果，则在查询结果中添加空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！