Can I treat a subquery with one row and one column as a scalar?

Question

Suppose I have the following SQL (which can be run against the Data Explorer, if you'd like):

SELECT COUNT(Id) AS "Count" 
  INTO #temp 
  FROM Posts
  
PRINT (SELECT * FROM #temp)

This produces an error:

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

Now, in this case, I know that #temp is a table of one row and one column, and hence that (SELECT * FROM #temp) will produce only one value. Is there any way to persuade SQL Server to treat it as a scalar?

I am aware that I can save it off to a variable and then PRINT that instead:

DECLARE @count int = (SELECT * FROM #temp)
PRINT @count

But this seems like an extra step that shouldn't be necessary.

Answer 1

No this isn't possible according to the grammar.

The only way of doing it other than assigning to a variable at the same scope would be to wrap the select in a UDF as far as I can see.

The documentation States

PRINT msg_str | @local_variable | string_expr

msg_str Is a character string or Unicode string constant. For more information, see Constants (Transact-SQL).

@ local_variable Is a variable of any valid character data type. @local_variable must be char, nchar, varchar, or nvarchar, or it must be able to be implicitly converted to those data types.

string_expr Is an expression that returns a string. Can include concatenated literal values, functions, and variables. For more information, see Expressions (Transact-SQL).

So assuming that "functions" includes user defined functions and not just built in functions this would work. Otherwise you're out of luck.

And for your specific use case you are certainly out of luck as, even ignoring the ridiculousness of creating a scalar UDF for this, they can't access temp tables anyway.