将表变量与 sp_executesql 一起使用 [英] using Table variable with sp_executesql

问题描述

我有一个包含表变量的查询:

I have a query that contain a table variable:

DECLARE @Selects    XML ;
SET @Selects='<Selects><Select><Q_ID>1</Q_ID><Q_DESC>nima1</Q_DESC></Select><Select><Q_ID>2</Q_ID><Q_DESC>nima2</Q_DESC></Select><Select><Q_ID>3</Q_ID><Q_DESC>nima3</Q_DESC></Select></Selects>'

DECLARE @QuestionID     NVARCHAR(10);
SET @QuestionID='a5';

DECLARE @TblSelect  TABLE 
(
    Q_ID            INT,
    Q_DESC          NVARCHAR(500)
)

INSERT INTO @TblSelect
(
    Q_ID,Q_DESC
)
SELECT  Q_Select.value('(Q_ID)[1]', 'int') AS 'Q_ID',
        Q_Select.value('(Q_DESC)[1]', 'nvarchar(500)') AS 'Q_DESC'
FROM    @Selects.nodes('/Selects/Select') AS AllSelects(Q_Select) 

DECLARE @Query      NVARCHAR(4000);
SET @Query=N'SELECT Q_ID,COUNT(Q_ID) FROM @TblSelect LEFT OUTER JOIN tblbase tb ON @TblSelect.Q_ID = @Col_Select group by Q_ID';

EXECUTE sp_executesql @Query,@TblSelect,@Col_Select

如何将表变量传递给我的查询?

How I can pass the table variable to my query?

推荐答案

以下是如何将表值参数传递给 sp_executesql 的示例.必须传递变量 readonly:

Here's an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:

if exists (select * from sys.types where name = 'TestTableType')
    drop type TestTableType

create type TestTableType as table (id int)
go
declare @t TestTableType
insert @t select 6*7

exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @t

这会打印Question of the Ultimate of Anwers, 2842.29"宇宙和一切.

这篇关于将表变量与 sp_executesql 一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！