无法将TVP用于动态SQL查询

Question

我尝试使用动态查询在TVP (表值参数)变量中插入值，并将TVP值传递到过程中。我得到的错误是-- Msg 1087，Level 15，State 2，Line 1必须声明表变量"@EmpDetailsTVP1“。不明白我哪里错了，我已经在上面声明了TVP

其中ee12345是TVP表参数cp1122334是Proced.

declare @vsql1 as nvarchar(max)
DECLARE @EmpDetailsTVP1 AS ee12345
set @vsql1='INSERT INTO @EmpDetailsTVP1(ei,en,enm)
           select EmpId1, EmpName1, [Role1] from EmployeeDetails2 '
exec sys.sp_executesql
    @vsql1
EXEC cp1122334 @EmpDetailsTVP 

Answer 1

表变量的作用域是声明的作用域。加载表变量，然后将其作为TVP传递。只需省略动态SQL，使其与declare处于相同的作用域：

DECLARE @vsql1 AS nvarchar(MAX);
DECLARE @EmpDetailsTVP1 AS ee12345;
INSERT INTO @EmpDetailsTVP1(ei,en,enm)
    SELECT EmpId1, EmpName1, [Role1] FROM dbo.EmployeeDetails2;
EXEC cp1122334 @EmpDetailsTVP;

Answer 2

Dynamic query将具有不同session，因此您不能使用在动态查询外部声明的表变量，

相反，您可以创建一个与TVP结构相同的temp table。

Create table #temp(ei datatype,en datatype...) -- As like `TVP(ee12345)`

declare @vsql1 as nvarchar(max)
DECLARE @EmpDetailsTVP1 AS ee12345

插入到可以在动态查询中访问的temp table(#temp)中，而不是TVP中

set @vsql1='INSERT INTO #temp(ei,en,enm)
           select EmpId1, EmpName1, [Role1] from EmployeeDetails2 '
exec sys.sp_executesql @vsql1

现在将记录插入回TVP

Insert into @EmpDetailsTVP (ei,en,enm)
select ei,en,enm from #temp

EXEC cp1122334 @EmpDetailsTVP