SQL 将表变量与 sp_executesql 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7329996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using Table variable with sp_executesql
提问by Arian
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?
如何将表变量传递给我的查询？
回答by Andomar
Here's an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:
下面是如何将表值参数传递给sp_executesql. 必须传递变量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
This prints the Answer to the Ultimate Question of Life, the Universe, and Everything.
这将打印生命、宇宙和一切终极问题的答案。
回答by Nighil
Try Table Type
尝试表类型
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 TYPE TblSelect AS TABLE
(
Q_ID INT,
Q_DESC NVARCHAR(500)
)
/* Declare a variable that references the type. */
DECLARE @TblSelect
AS TblSelect ;
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
