Is there any way to define type inline based on existing table?
The feaure is called: anchored data type and there is no direct equivalent in T-SQL.
An anchored type defines a data type based on another SQL object such as a column, global variable, SQL variable, SQL parameter, or the row of a table or view.
A data type defined using an anchored type definition maintains a dependency on the object to which it is anchored. Any change in the data type of the anchor object will impact the anchored data type. If anchored to the row of a table or view, the anchored data type is ROW with the fields defined by the columns of the anchor table or anchor view.
This data type is useful when declaring variables in cases where you require that the variable have the same data type as another object
It is supported by IBM DB2 and Oracle DBs.
Alternative:
Pass argument as XML/JSON instead of table valued paratemer. It will ease a burden of maitining type for procedures.
CREATE PROCEDURE my_proc @param XML -- instead of table type
AS
BEGIN
-- here @param validation could be performed if needed
SELECT s.c.value('col_name1', type_name) AS col_name1
,s.c.value('col_name2', type_name) AS col_name2
INTO #tmp_param
FROM @param p
CROSS APPLY p.nodes('...') s(c)
SELECT *
FROM #tmp_param; -- any other use like normal table valued parameter
END
And calling it:
--Standard way
DECLARE @param table_type;
INSERT INTO @param(col1, col2) ...;
EXEC my_proc @param;
-- different way
DECLARE @param XML = (SELECT ... FROM ... FOR XML AUTO);
EXEC my_proc @param;
The only drawback here is there is no param schema validation, and it has to be performed inside stored procedure.
SELECT ... INTO ...?