Create and Pass a Table-Valued Parameter in a Single Line

Question

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 a stored procedure (SP) that takes a TVP and a working example to execute that SP where I have to declare the TVP, populate it and then pass it into the SP. I would like to be able to simply pass in the population criteria directly to the EXEC call. Is this possible?

Scenario Setup:

-- Create a sample Users table
CREATE TABLE Users (UserID int, UserName varchar(20))
INSERT INTO Users VALUES (1, 'Bob'), (2, 'Mary'), (3, 'John'), (4, 'Mark')

-- Create a TVP Type
CREATE TYPE UserIdTableType AS TABLE (UserID int)

-- Create SP That Uses TVP Type
CREATE PROCEDURE GetUsers
@UserIdFilter UserIdTableType READONLY
AS
    SELECT * FROM @UserIdFilter WHERE UserID > 2

Working Method to Execute:

DECLARE @MyIds AS UserIdTableType
INSERT INTO @MyIds SELECT UserID FROM Users
EXEC GetUsers @MyIds

Requested Method to Execute:

EXEC GetUsers (SELECT UserID FROM Users)

Answer 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 + '%'
ORDER BY tmp.[object_id] ASC;