SQL Server: Is it possible to UNION two tables in a SELECT statement without using a temp table?

I am creating 开发者_JAVA技巧a stored procedure that sets 3 variables based on queries and I have to return the MAX value of them. I am trying to do so without using a temp table however the following gives me the error Incorrect styntax near ')'

SELECT MAX(PermissionID)
FROM (SELECT @ContactPermission As PermissionID UNION ALL
      SELECT @GroupPermission As PermissionID)

I have also tried

SELECT MAX((SELECT @ContactPermission UNION ALL SELECT @GroupPermission))

and I get the error Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I have also tried bypassing the variables and using a UNION in a subquery...

SELECT MAX(PermissionID)
FROM (SELECT PermissionID
        FROM PermissionContact
        WHERE ContactID = @ContactID
      UNION ALL
      SELECT PermissionID
      FROM PermissionGroup
      WHERE GroupID = @GroupID)

This also gives the Incorrect syntax near ')'

Any thoughts or suggestions? Are temp tables my only option?

You need to give your derived table an alias.

SELECT MAX(PermissionID)
FROM (SELECT @ContactPermission As PermissionID UNION ALL
      SELECT @GroupPermission As PermissionID) as T

Or just use a case statement

Select Case When @ContactPermission > @GroupPermission 
       Then @ContactPermission Else @GroupPermission End PermissionID

or,

SELECT Case When c.PermissionID > g.PermissionId
        Then c.PermissionID Else g.PermissionId End
FROM PermissionContact c Cross Join PermissionGroup g   
Where c.ContactID = @ContactID
    And g.GroupID = @GroupID