执行查询直到非空结果

I have three queries, looking like these:

我有三个查询,看起来像这样:

SELECT * FROM Table1 WHERE Column1 = 'a'
SELECT * FROM Table2 WHERE Column2 = 'b'
SELECT * FROM Table1 A, Table2 B WHERE A.Column1 <> B.Column1

Now all logic is implemented on the client side as following. Execute the first query, if HasRows, set a flag to 1 and return the rows. Otherwise execute the second query, if HasRows, set the flag to 2 and return the rows. Otherwise execute the third query, set the flag to 3 and return the rows.

现在所有逻辑都在客户端实现,如下所示。执行第一个查询,如果是HasRows,则将标志设置为1并返回行。否则执行第二个查询,如果是HasRows,则将标志设置为2并返回行。否则执行第三个查询,将标志设置为3并返回行。

How to do this with a single query? Flag stuff, I guess, should be solved adding Flag to the queries:

如何使用单个查询执行此操作?我想,应该解决标志的问题,在查询中添加Flag:

SELECT Flag = 1, * FROM Table1 WHERE Column1 = 'a'
SELECT Flag = 2, * FROM Table2 WHERE Column2 = 'b'
SELECT Flag = 3, * FROM Table1 A, Table2 B WHERE A.Column1 <> B.Column1

But now what? How to check, if a query returns non-empty result?

但现在呢?如果查询返回非空结果,如何检查?

Also, I'd like to cache the results, in other words, to avoid executing the same query twice - once for checking and the second time - for returning data.

另外,我想缓存结果,换句话说,为了避免两次执行相同的查询 - 一次用于检查,第二次用于返回数据。

Regards,

1 个解决方案

#1

You could use a table variable to store the result and only return it at the end of the SQL block. Checking @@rowcount would tell you if the previous insert added any rows; if it's zero, you can run further queries:

您可以使用表变量来存储结果,并仅在SQL块的末尾返回它。检查@@ rowcount会告诉您前一个插入是否添加了任何行;如果它为零,则可以运行进一步的查询:

declare @result table (flag int, col1 int, col2 varchar(50))
insert @result select 1, col1, col2 from Table1 where Column1 = 'a'
if @@rowcount = 0 
    begin
    insert @result select 2, col1, col2 from Table2 where Column1 = 'b'
    end
if @@rowcount = 0 
    begin
    insert @result select 3, col1, col2 from Table1 A, Table2 B 
        where A.Column1 <> B.Column1
    end

select * from @result

This approach only works if each select has the same column definition.

仅当每个select具有相同的列定义时,此方法才有效。