从存储过程中的动态 SQL 获取结果

问题描述

我正在编写一个存储过程，我需要在该过程中动态构造一个 SQL 语句来引用传入的表名.

I'm writing a stored procedure where I need to dynamically construct a SQL statement within the procedure to reference a passed in table name.

我需要让这个 SQL 语句返回一个结果，然后我可以在整个过程的其余部分使用该结果.

I need to have this SQL statement return a result that I can then use throughout the rest of the procedure.

我尝试过使用临时表和所有东西，但我不断收到一条消息，提示我需要声明变量等.

I've tried using temp tables and everything but I keep getting a message that I need to declare the variable, etc.

例如:

DECLARE @FiscalYear INT
DECLARE @DataSource NVARCHAR(25)
DECLARE @SQL NVARCHAR(250)
SELECT @DataSource = 'CustomerCosts20120328'
DECLARE @tempFiscalYear TABLE ( FiscalYear INT )
SELECT @SQL = 'INSERT INTO @tempFiscalYear SELECT DISTINCT FiscalYear FROM ' + @DataSource
EXEC(@SQL)
SELECT @FiscalYear = FiscalYear FROM @tempFiscalYear

或者...

DECLARE @FiscalYear INT
DECLARE @DataSource NVARCHAR(25)
DECLARE @SQL NVARCHAR(250)
SELECT @DataSource = 'CustomerCosts20120328'
SELECT @SQL = 'SELECT DISTINCT @FiscalYear = FiscalYear FROM ' + @DataSource
EXEC(@SQL)

有没有办法在不使用实际表格的情况下做到这一点?

Is there anyway to do this without resorting to using an actual table?

谢谢.

推荐答案

您是否尝试过类似的方法:

Did you try something like:

DECLARE @FiscalYear INT, @DataSource NVARCHAR(25), @SQL NVARCHAR(250);
SET @DataSource = N'CustomerCosts20120328';
SET @SQL = N'SELECT DISTINCT @FiscalYear = FiscalYear FROM ' + @DataSource;
EXEC sp_executesql @SQL, N'@FiscalYear INT OUTPUT', @FiscalYear OUTPUT;

PRINT @FiscalYear;

您需要确保在 nvarchar 字符串前加上 N，例如SELECT @SQL = N'SELECT ....

You'll want to make sure you prefix nvarchar strings with N, e.g. SELECT @SQL = N'SELECT ....

此外，您知道如果查询返回多行，分配给 @FiscalYear 的值是完全任意的，对吧?虽然您可能期望该表中只有一个值，但使用 MAX() 或 TOP 1 ... ORDER BY 来确保只有一个，可预测的值被分配.

Also, you know that if the query returns multiple rows, the value that gets assigned to @FiscalYear is completely arbitrary, right? While you may expect a single value from that table, it can't hurt to use MAX() or TOP 1 ... ORDER BY to ensure that only a single, predictable value is ever assigned.

这篇关于从存储过程中的动态 SQL 获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！