sql - How can I retrieve information from two different tables in a stored procedure? -

i have develop db stored procedure used in autocomplete feature. if user types "m" should return people , groups name starting "m".

i in way system should need call 1 stored procedure avoid cost of calling db twice.

my problem groups , people in separate table (i need , and id), ideally do:

select id, name people or group name 'm%'

of course not valid sql. thought creating temp table, making 2 selects (one people , 1 groups), inserting in table , returning it.

is there simpler/nicer way implement or best approach?

just perform union all. 2 separate queries, temp table, multiple inserts, , 3rd query, doesn't sound whole lot of unnecessary work?

create procedure dbo.searchtwotables   @search_argument nvarchar(255) = n'm%' begin   set nocount on;    select id, name dbo.people      name @search_argument   union   select id, name dbo.group     name @search_argument; end  go 

also assume name column should support unicode strings (and therefore nvarchar), otherwise may find angry person when mangle name upon saving...