如何将 sp_executesql 结果放入变量中? [英] How to get sp_executesql result into a variable?

问题描述

我有一段动态 SQL 需要执行，然后我需要将结果存储到一个变量中.

I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.

我知道我可以使用 sp_executesql 但找不到关于如何执行此操作的明确示例.

I know I can use sp_executesql but can't find clear examples around about how to do this.

推荐答案

如果你有 OUTPUT 参数你可以做

If you have OUTPUT parameters you can do

DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @tablename nvarchar(50)  
SELECT @tablename = N'products'  

SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

SELECT @retval;

但是如果你不这样做，也不能修改SP:

But if you don't, and can not modify the SP:

-- Assuming that your SP return 1 value
create table #temptable (ID int null)
insert into #temptable exec mysp 'Value1', 'Value2'
select * from #temptable

不漂亮，但有效.

这篇关于如何将 sp_executesql 结果放入变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！