How to take the top 10 results of a UNION in sql?

I currently have the following code in a stored procedure (see below). In an effort to return 10 results total, I take the TOP 5 of each union-half. However, I'd like to take the TOP 10 of the UNION, and not necessarily 5 of each. Any ideas? Is this possible?

BEGIN
SELECT TOP 5
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'

UNION ALL

SELECT TOP 5 
    b_object_ID as [id], 开发者_StackOverflow
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'

ORDER BY [name]
END

Yes, very much possible. You just need to select top 10 from the union results.

SELECT TOP 10 * FROM (
    SELECT
        a_object_ID as [id], 
        a_object_name as [name],
        'A_object' as [Type]
    FROM [database].[dbo].[table_a]
    WHERE a_object_name LIKE @Search + '%'

    UNION ALL

    SELECT
        b_object_ID as [id], 
        b_object_name as [name],
        'B_object' as [Type]
    FROM [database].[dbo].[table_b]
    WHERE b_object_name LIKE @Search + '%'
) u
ORDER BY u.[name]

Make your UNION a subquery

SELECT TOP 10 * 
FROM (
  SELECT
      a_object_ID as [id], 
      a_object_name as [name],
      'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'

  UNION ALL

  SELECT
      b_object_ID as [id], 
      b_object_name as [name],
      'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
) AS subquery
ORDER BY subquery.[name]

How about this?

SELECT TOP 10 *
FROM
(
SELECT a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
UNION ALL
SELECT b_object_ID as [id], 
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'
) x
ORDER BY [name]

Why complicate the query? This will do the job:

set rowcount 10

  SELECT a_object_ID as [id], 
         a_object_name as [name],
         'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'
UNION ALL
  SELECT b_object_ID as [id], 
         b_object_name as [name],
         'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
ORDER BY [name]

set rowcount 0

The above answers are wasteful as the subqueries collect then reject potentially large numbers of rows. You can improve on matters using subqueries.

declare @Recs int = 10;

with cte1 as (
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
, cte2 as
(
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
SELECT TOP (@Recs) [id], [name], [Type]
(
select [id], [name], [Type] from cte1
union all
select [id], [name], [Type] from cte2
) SQ
ORDER BY [Name]