Can you create a table param WITHOUT a pre-defined TVP in a SP?

In the body of an SP I have the following line of code:

DECLARE @assetCode NVARCHAR(50) = ( SELECT DISTINCT [Asset Code] FROM  WHERE hProp = xxx )

Is it possible to do the same with a TABLE variable type? i.e. something along the lines of:

DECLARE @tbl TABLE = (SELECT x,y,z FROM ....)

I have seen something similar to this in a Table-valued function:

RETURNS TABLE 
AS
RETURN 
(
    SELECT 
    *
    FROM  
    (   SELECT
        ... etc

No, that's not possible at this time.

Microsoft recently added the Create Table As syntax to Azure SQL Data Warehouse, but it has not made it to Azure SQL, or the boxed product yet (AFAIK, it's not even in vNext at the moment). Even if it does, it's not clear if it would be supported for table variables like in your example.

Further, SELECT INTO is also not available for table variables, as it is for temp tables and permanent tables.

SELECT TOP 1000 *
INTO @dingdong --Doesn't work
FROM dbo.Users AS u

SELECT TOP 1000 *
INTO #dingdong --Works
FROM dbo.Users AS u

Edit to answer the edit to your question: The syntax you're talking about is for an inline table valued function, which looks like this (from the docs).


-- Transact-SQL Inline Table-Valued Function Syntax

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ {
@parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,...n ] ] ) RETURNS TABLE

[ WITH [ ,...n ] ]

[ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

StackExchange Database Administrators Q#167886, answer score: 3