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)

It is a TVP that procedure is expecting not a select query, You will need to declare a TVP specifically of a type that this procedure accepts , populate it and then pass it to the proc , you cannot simple just pass a select query instead of the TVP. — M.Ali
– M.Ali, Commented Feb 8, 2015 at 21:23
@M.All, is there any way to cast it as a TVP in a single line? Something like EXEC GetUsers CAST((SELECT UserID FROM Users) as UserIdTableType)? I'm just looking for a shortcut way to make the calling code look cleaner. — bigmac
– bigmac, Commented Feb 8, 2015 at 21:28
nope you cannot but you can pass your query as it is , as a parameter and execute it inside your proc, you will need to use dynamic sql for that. — M.Ali
– M.Ali, Commented Feb 8, 2015 at 21:31
@bigmac Just curious if you had a chance to try this out. It does avoid dynamic SQL :-). — Solomon Rutzky
– Solomon Rutzky, Commented Aug 25, 2015 at 21:35

Solomon Rutzky · Accepted Answer · 2015-02-09 06:04:34Z

5

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;

answered Feb 9, 2015 at 6:04

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jon Schneider Over a year ago

Is the "dump the results to a temp table and just refer to it in the sub-proc" approach thread-safe?

Solomon Rutzky Over a year ago

@JonSchneider I don't see how it couldn't be. A local temp table is local to the Session it is created in. Even with connection pooling keeping that same Session, the first action upon coming back to that Session is to run an internal stored procedure that wipes out any prior local temp tables. Also, I have worked on systems with hundreds to thousands of transactions per second, doing stuff like this (though not terribly frequently), and all was fine.

Jon Schneider Over a year ago

Thanks! I wasn't sure how/whether SQL Server shared temp tables between sessions.

Solomon Rutzky Over a year ago

@JonSchneider Yer welcome. And a whole mess of stuff would break if sessions, even re-used connections from a pool on the same Session ID, shared local temp tables. And there is no ability for them to be shared between session since if you look at tempdb.sys.tables you will see that each local temp table name has a hash code appended to it, providing that separation.

Nickolay Over a year ago

Note that this approach will recompile the query in the inner Proc_4 for each execution of the outer proc (1/2/3). See this (scroll to "Now consider a case when the temporary table is referenced in a second stored procedure as below:"). And when you turn back to TVPs with their verbosity, keep in mind that they don't carry statistics and are subject to "parameter sniffing"..

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related