sql-server - 在一行中创建并传递表值参数

Question

使用 SQL Server 2012，是否可以无需声明表值参数 (TVP)，只需将其传递到存储过程中？下面是一个非常简单的存储过程 (SP) 示例，它采用 TVP 和执行该 SP 的工作示例，其中我必须声明 TVP、填充它，然后将其传递到 SP。我希望能够简单地将人口标准直接传递给 EXEC 调用。这可能吗？

场景设置:

-- 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

执行的工作方法:

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

请求的执行方法:

EXEC GetUsers (SELECT UserID FROM Users)

Answer 1

不，您无法创建内联 TVP 或对其进行 CAST/CONVERT。它不是像INT、VARCHAR、DATETIME等那样的“数据类型”；它是完全不同的“表类型”。用户定义的表类型 (UDTT) 只是用作表变量声明的定义/模式的元数据。当这样的表变量用作输入参数时，该用法被视为 TVP(表值参数)。但它仍然是一个表变量，其定义存储在 tempdb 中。这是一个物理结构，而不是内存结构，您无法CAST或CONVERT表，无论它是真实的、临时的还是变量。

虽然问题中给出的示例很简单，只是为了表达想法，但您的总体目标似乎是代码重用/创建子例程(否则您可以轻松完成SELECT * FROM Users WHERE UserID > 2)。不幸的是，T-SQL 不允许真正优雅/干净的代码，因此您将不得不接受一定程度的重复和/或笨拙。

但是，可以为结果集创建稍微通用的处理程序，只要它们至少具有必需的字段。你也可以

• 传入 XML 参数，或
• 将结果转储到临时表中，只需在子过程调用中引用它(不需要是动态 SQL)，因此不需要传递任何参数(至少不需要传递数据集/结果)/查询)

在这两种情况下，结构都比使用 TVP 更灵活，因为 TVP 必须是那些确切的字段。但是引用假定存在的临时表可以实现类似于以下内容的操作:

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;