在一行中创建并传递表值参数 [英] Create and Pass a Table-Valued Parameter in a Single Line

问题描述

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

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?

场景设置:

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

推荐答案

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

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.

虽然问题中给出的例子很简单，只是为了理解这个想法，但看起来你的总体目标是代码重用/创建子例程(否则你可以很容易地完成 SELECT * FROM UsersWHERE UserID > 2).不幸的是，T-SQL 不允许真正优雅/干净的代码，因此您将不得不接受一定程度的重复和/或笨拙.

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

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

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

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;

这篇关于在一行中创建并传递表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！