从动态 SQL 获取输出参数值 [英] Obtain output parameter values from dynamic SQL

问题描述

declare @var varchar(max),@course varchar(max)
set @var='ABC'
set @Query=''
set @Query='  
select @course=PARAM from TABLE where PARAM='''+@var+''''
print @Query
exec (@Query)

由于上述查询返回错误为

Since the above query returns an error as

必须声明标量变量@course"

Must declare the scalar variable "@course"

此处的查询是我现在正在遵循的替代方法，以使该查询成功.

The query here is the alternative I am following right now to make that query successful.

declare @var varchar(max),@course varchar(max),@Query varchar(max)
Create table #temp(param1 varchar(max))
set @var='ABC'
set @Query=''
set @Query='insert #temp(param1)
select PARAM from TABLE where PARAM='''+@var+''''
print @Query
exec (@Query)
select @course=param1 from #temp
drop table #temp

除了我上面提到的解决方案之外，还有其他更好的选择吗?

Is there any other better alternative to this other than the solution I have mentioned above?

推荐答案

好吧，我不确定您是否真的需要在这里使用动态 SQL，但我怀疑您为我们简化了一个更复杂的示例.(提示:您不需要这样做.)

Well I'm not sure that you actually need to use dynamic SQL here, but I suspect that you dumbed down a more complex example for us. (Tip: you don't need to do that.)

如果这就是你所做的一切，那为什么不只是:

If this is all you're doing, then why not just:

SELECT @course = PARAM FROM dbo.Table WHERE PARAM = @var;

(这甚至没有意义 - 根据定义，@course 和 @var 要么相等，要么该行不存在.)

(Which doesn't even make sense - by definition @course and @var are either equal or the row doesn't exist.)

如果您的 SQL 实际上更复杂，那么您需要停止使用 EXEC() 并使用 sp_executesql.这允许的一件事是使用强类型参数(包括 OUTPUT)具有更大的灵活性.这是一个简单的例子:

If your SQL is actually more complicated, then you need to STOP using EXEC() and embrace sp_executesql. One of the things this allows is much more flexibility with strongly-typed parameters (including OUTPUT). Here is a quick example:

DECLARE @table_name SYSNAME;

DECLARE @var VARCHAR(MAX), @course VARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @var = 'ABC', @table_name = N'TABLE_2012';

SET @sql = N'SELECT @course = PARAM FROM ' + @table_name 
  + ' WHERE PARAM = @var;'

EXEC sp_executesql @sql,
  N'@var VARCHAR(MAX),@course VARCHAR(MAX) OUTPUT',
  @var, @course OUTPUT;

PRINT @course;

这篇关于从动态 SQL 获取输出参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！