You can use derived table

SELECT * FROM (
     select * from dbo.SystemMessage union all select * from dbo.ApplicationMessage 
  ) dt1 WHERE MessageID = @lnMessageID

>
>I'm performing the following stored procedure to essentially join 2 tables, with identical structures, to amalgamate the list of system/application messages into one dataset:
>
>as
>begin
>set nocount on;
>select * from dbo.SystemMessage union all select * from dbo.ApplicationMessage order by MessageID
>end
>
>I now want to pass a parameter into this stored procedure, @lnMessageID, and I'd like the statement to perform the join and THEN return the record from the joined results that matches @lnMessageID. @lnMessageID is always unique.
>
>Regards