Selecting from two non-related tables at the same time
===

Suppose I have a table with lots of rows and columns (alias: bigtable), and a table that always has 1 row, but multiple columns (alias: 1rowtable). The 1rowtable has nothing to do with bigtable, it is just there for some settings my script uses that are modified dynamically. So I cannot use static SQLCMD variables for that and I cannot use normal variables for that either because my script has GO statements.

Now I want to write a select statement that accesses BOTH tables.

If I do:

    SELECT ... FROM bigtable, 1rowtable

it does a CROSS JOIN so that is bad, can't go that route.

If I use a CTE for 1rowtable, I have to access its fields with

    SELECT field FROM 1rowtable

So that is bad too. Same with a table valued function like:

    CREATE FUNCTION getSetting(@name nvarchar(40))
    RETURNS TABLE
    AS
    RETURN (SELECT name FROM 1rowtable WHERE name = @name)

Obviously I cannot use a scalar function at all because it only returns a specific datatype, but the settings have different datatypes. Yet, obviously I would like to use it LIKE a scalar function of course without doing the 'SELECT .. FROM dbo.getfieldfrom1rowtable(..)' stuff, since I am using the 1rowtable rather often in queries.

I also tried doing:

    SELECT
      (SELECT
         <expression involving bigtable and 1rowtable>,
         <expression involving bigtable and 1rowtable>,
         <expression involving bigtable and 1rowtable>,
         ...
      FROM 1rowtable)
    FROM bigtable

But of course a subselect cannot select more than one item if it does not begin with exists...

So what should I do? It seems I will have to continue using 'SELECT .. FROM dbo.getfieldfrom1rowtable(..)' every time? Just curious :)

PS. ms sql server 2008r2