sql conditional union on rowcount

I have an SQL function which returns a list of Teams.

I want to join an additional list to that list with a union, b开发者_如何学JAVAut only if first select returns more than one rows.

Something like:

CREATE FUNCTION Teams()
RETURNS TABLE
AS
RETURN
(
  SELECT * FROM TABLE1
  if @@rowcount>1      
  UNION 
  SELECT * FROM TABLE2
  end if
)

Not pretty but this should work:

CREATE FUNCTION Teams() 
RETURNS TABLE 
AS 
RETURN 
( 
  SELECT * FROM TABLE1 
  UNION  
  SELECT * FROM TABLE2 WHERE EXISTS (SELECT * FROM TABLE1)
 ) 

If the select from the first table is complicated you could put it into a CTE:

CREATE FUNCTION Teams() 
RETURNS TABLE 
AS 
RETURN 
( 
  WITH Result AS
  (
      SELECT * FROM TABLE1 WHERE ComplicatedConditions = 1
  )
  SELECT * FROM Result
  UNION  
  SELECT * FROM TABLE2 WHERE EXISTS (SELECT * FROM Result)
 ) 

If I understand correctly

CREATE FUNCTION Teams()
RETURNS TABLE
AS
RETURN
( 
  if (select COUNT(*) from TABLE2)>1  
   SELECT * FROM TABLE1
   UNION 
   SELECT * FROM TABLE2
  else
   SELECT * FROM TABLE1
 )

One way would be to do:

IF EXISTS(SELECT 1 FROM TABLE1)
    SELECT * FROM TABLE1
    UNION 
    SELECT * FROM TABLE2
ELSE
    SELECT * FROM TABLE1