I have a query that us开发者_JAVA百科es a UNION ALL to return results from two different select statements.
I would like to set this up to return only the first select results, and if this first select is empty then return the results of the second select statement.
Can someone give me a simple example of how I might do this.
Thanks in advance Brian
This checks your first query for a record returned, then executes it. Otherwise it executes the second query.
DECLARE @FirstCount int
SET @FirstCount = (SELECT COUNT (*) FROM <First query>)
IF @FirstCount > 0
BEGIN
<first query here>
END
ELSE
BEGIN
<Second query here>
END
You can also use a table variable like so:
DECLARE @FirstQuery TABLE
(
<fields>
)
SET @FirstQuery = <First Query>
IF EXISTS (SELECT * FROM @FirstQuery)
BEGIN
SELECT * FROM @FirstQuery
END
ELSE
BEGIN
<Second query here>
END
Assuming that you want a query and not a batch, you could duplicate the 1st query of your Union in the second query to determine if it yielded results, and make the 2nd query conditional on that. E.g. only show shipments if there are no order, otherwise show both (makes no business sense, but shows the point):
select id from tb_shipment
union all
select id from tb_order where 0 = (select COUNT(*) from tb_shipment)
Without knowing your data, I cannot say if there are more efficient ways for you than a WHERE clause.
精彩评论