T 如何使用 EXEC 或 EXECUTE sp_executesql存储动态查询的结果

Question

我正在尝试编写动态查询。 假设我有一个如下所示的表，它表示销售代理的层次结构级别：

AgentNumber Level1Agent Level2Agent Level3Agent Level4Agent Level5Agent
1122334455  1122334499  1122334488  1122334477  1122334466  1122334455

我希望能够根据指定的代理动态选择级别。 我的 EXECUTE 语句似乎工作正常，但是如何将结果存储在以后可以使用的变量中？ 我找到的每个答案似乎都只能让我获得一个成功的返回变量，而不是实际的查询结果。

下面是我的代码：

DECLARE @level INT = 1;
DECLARE @agent CHAR(10) = 1122334455;
DECLARE @colname NVARCHAR(11) = CONCAT('Level',@level,'Agent');
DECLARE @whereclause NVARCHAR(35) = CONCAT('WHERE AgentNumber = ',@agent);
DECLARE @qry NVARCHAR(300) = 'SELECT ' + @colname + ' FROM dbo.TABLE ' + @whereclause;
DECLARE @up NVARCHAR(10);
EXECUTE sp_executesql @qry, @up OUT
SELECT @up

@up的输出为 NULL。 如果我将最后两行更改为：

EXECUTE @up = sp_executesql @qry
SELECT @up

现在@up的输出为 0。

我想要1122334499的输出，我需要将其存储在一个变量中，以后可以使用该变量并将其插入到表中。

Answer 1

下面是一个功能齐全的示例，说明如何执行此操作。请注意，这是在动态 sql 中的列名周围使用参数化的 where 子句和引号，以防止 sql 注入。

if OBJECT_ID('tempdb..#Agents') is not null
    drop table #Agents
create table  #Agents 
(
    AgentNumber char(10)
    , Level1Agent char(10)
    , Level2Agent char(10)
    , Level3Agent char(10)
    , Level4Agent char(10)
    , Level5Agent char(10)
)
insert #Agents
select '1122334455', '1122334499', '1122334488', '1122334477', '1122334466', '1122334455'
DECLARE @level INT = 3;
DECLARE @agent CHAR(10) = 1122334455;
DECLARE @colname NVARCHAR(11) = CONCAT('Level',@level,'Agent');
declare @agentout char(10)
DECLARE @qry NVARCHAR(300) = 'SELECT @agent_out = ' + quotename(@colname) + ' FROM #Agents WHERE AgentNumber = @agentin';
EXECUTE sp_executesql @qry, N'@agentin char(10), @agent_out char(10) output', @agentin = @agent, @agent_out = @agentout output
select @agentout

Answer 2

你可以试试这个：

DECLARE @level INT = 1;
DECLARE @agent CHAR(10) = 1122334455;
DECLARE @colname NVARCHAR(11) = CONCAT('Level',@level,'Agent');
DECLARE @whereclause NVARCHAR(35) = CONCAT('WHERE AgentNumber = ',@agent);
DECLARE @qry NVARCHAR(300) = 'SELECT @agentout=' + @colname + ' FROM dbo.TABLE ' + @whereclause;
DECLARE @up NVARCHAR(10);
EXECUTE sp_executesql @qry, N'@agentout NVARCHAR(10) OUTPUT', @agentout=@up OUTPUT
SELECT @up

Answer 3

创建一个变量表，并使查询在其中插入所需的结果。像这样：

declare @results table(field1 varchar(max), field2 varchar(max));
declare @sqlStatement varchar(max);
set @sqlStatement = 'insert into @results(field1, field2) select field1, field2 from table';

EXECUTE @sqlStatement;
select * from @results; --It will print the results from your sql statement!