Create And Pass A Table-valued Parameter In A Single Line

Using SQL Server 2012, is it possible to eliminate the need to declare a table-valued parameter (TVP) just to pass it into a stored procedure? Below is a really simple example of

Solution 1:

No, you cannot create a TVP inline or CAST / CONVERT it. It is not a "Data Type" like INT, VARCHAR, DATETIME, etc.; it is a "Table Type" which is entirely different. The User-Defined Table Type (UDTT) is just meta-data that is used as the definition/schema for the declaration of a Table Variable. When such a Table Variable is used as an input parameter, that usage is considered a TVP (Table-Valued Parameter). But the thing is still a Table Variable which has its definition stored in tempdb. This is a physical structure, not a memory structure, and you can't CAST or CONVERT a Table, whether it is real, temporary, or a variable.

While the example given in the Question is simplistic for the sake of just getting the idea across, it does seem like your overall goal is code-reuse / creating subroutines (else you could have easily done SELECT * FROM Users WHERE UserID > 2). Unfortunately T-SQL doesn't allow for really elegant / clean code, so you will have to accept a certain level of repetition and/or clunkiness.

It is possible, however, to make slightly generic handlers for result sets, provided they at least have the required fields. You could either

• pass in an XML parameter, or
• dump the results to a temp table and just refer to it in the sub-proc call (doesn't need to be dynamic SQL) and hence no need to pass in any parameter (at least not one for the dataset / results / query)

In both of those cases, the structure is more flexible than using a TVP since the TVP has to be those exact fields. But referencing a temp table that is assumed to exist allows for something similar to the following:

Proc_1

SELECT*INTO #MyTemp
FROM sys.tables;

EXEC dbo.Proc_4 @StartsWith='a', @HowMany=10;

Proc_2

SELECT*INTO #MyTemp
FROM sys.columns;

EXEC dbo.Proc_4 @StartsWith='bb', @HowMany=20;

Proc_3

SELECT*INTO #MyTemp
FROM sys.views;

EXEC dbo.Proc_4 @StartsWith='ccc', @HowMany=33;

Proc_4

SELECT TOP (@HowMany) tmp.*FROM   #MyTemp tmp
WHERE  tmp.[name] LIKE@StartsWith+'%'ORDERBY tmp.[object_id] ASC;