时间戳

首页 > 解决方案 > 动态变量 T-SQL（使用存储过程）

sql - 动态变量 T-SQL（使用存储过程）

问题描述

所以我有一个带参数的表值函数：

SampleProcedure(@date,@par1,@par2,@par3)

日期变量是一个 INT ，例如：

@date int = 20170102

我想做的是迭代接下来的几天，直到 EOF 或特定的预定义 date ，所以一旦上一次迭代完成，@date 变量应该改变。其他参数不变。

我应该采取什么方法？我想知道我是否应该使用 cursors ，但目前我并不真正理解它们 - 如果有人在这个例子中解释我将不胜感激（将日期迭代为整数）。

编辑 ：

更具体的案例：

我有 GetDailyUsageReal 和 GetDailyUsageForecast 存储过程。

我的输入：

DECLARE @date int = 20170102,
@par1 INT = 4000,
@par2 INT = 1,

;WITH CTE as (SELECT Hour, SUM(CAST(UsReal AS DECIMAL(19, 6))) / 1000000 as Real, Day
 FROM GetDailyUsageReal(@date,@par1,@par2)
Group BY Hour,Day),

CTE2 as (SELECT Hour, SUM(CAST(UsForecast AS DECIMAL(19, 6))) / 1000000 as Forecast, Day
 FROM GetDailyUsageForecast(@date,@par1,@par2)
Group BY Hour,Day)

SELECT cte.Hour, Real, cte2.Forecast , cte.Day
FROM CTE
JOIN CTE2 on cte.hour=cte2.hour AND cte.day=cte2.day
ORDER BY cte.hour

输出是：

+------+------+----------+----------+--+
| Hour | Real | Forecast |   Day    |  |
+------+------+----------+----------+--+
|    1 |   10 |       12 | 20170102 |  |
|    5 |   24 |       23 | 20170102 |  |
|    7 |   24 |       22 | 20170102 |  |
|    8 |   27 |       27 | 20170102 |  |
|    9 |   26 |       21 | 20170102 |  |
|   10 |   21 |       21 | 20170102 |  |
|   11 |   11 |       12 | 20170102 |  |
|   12 |   25 |       24 | 20170102 |  |
|   13 |   17 |       18 | 20170102 |  |
|   14 |   18 |       19 | 20170102 |  |
|   15 |   26 |       25 | 20170102 |  |
|   16 |   22 |       21 | 20170102 |  |
|   17 |   23 |       23 | 20170102 |  |
|   18 |   24 |       23 | 20170102 |  |
|   19 |   19 |       18 | 20170102 |  |
|   20 |   10 |       11 | 20170102 |  |
|   21 |   11 |       13 | 20170102 |  |
|   22 |   18 |       16 | 20170102 |  |
|   23 |   19 |       17 | 20170102 |  |
|   24 |   11 |       13 | 20170102 |  |
+------+------+----------+----------+--+

我想要得到的基本上是接下来几天的输出，比如说直到 2019 年（我的数据库中甚至还有 2019 年的一些数据）。

所以我需要的是日期的迭代。我无权将@date 数据类型更改为 DATE。

@EDIT2：

我的预期输出：

+------+------+----------+----------+--+
| Hour | Real | Forecast |   Day    |  |
+------+------+----------+----------+--+
|    1 |   10 |       12 | 20170102 |  |
|    5 |   24 |       23 | 20170102 |  |
|    7 |   24 |       22 | 20170102 |  |
|    8 |   27 |       27 | 20170102 |  |
|    9 |   26 |       21 | 20170102 |  |
|   10 |   21 |       21 | 20170102 |  |
|   11 |   11 |       12 | 20170102 |  |
|   12 |   25 |       24 | 20170102 |  |
|   13 |   17 |       18 | 20170102 |  |
|   14 |   18 |       19 | 20170102 |  |
|   15 |   26 |       25 | 20170102 |  |
|   16 |   22 |       21 | 20170102 |  |
|   17 |   23 |       23 | 20170102 |  |
|   18 |   24 |       23 | 20170102 |  |
|   19 |   19 |       18 | 20170102 |  |
|   20 |   10 |       11 | 20170102 |  |
|   21 |   11 |       13 | 20170102 |  |
|   22 |   18 |       16 | 20170102 |  |
|   23 |   19 |       17 | 20170102 |  |
|   24 |   11 |       13 | 20170102 |  |
|    1 |   15 |       14 | 20170103 |  |
|    5 |   18 |       11 | 20170103 |  |
|    7 |   26 |       44 | 20170103 |  |
|    8 |   21 |       33 | 20170103 |  |
|    9 |   22 |       12 | 20170103 |  |
|   10 |   21 |       21 | 20170103 |  |
|   11 |   11 |       12 | 20170103 |  |
|   12 |   15 |       12 | 20170103 |  |
|   13 |   17 |       18 | 20170103 |  |
|   14 |   18 |       19 | 20170103 |  |
|   15 |   26 |       25 | 20170103 |  |
|   16 |   22 |       21 | 20170103 |  |
|   17 |   23 |       23 | 20170103 |  |
|   18 |   24 |       23 | 20170103 |  |
|   19 |   19 |       18 | 20170103 |  |
|   20 |   10 |       11 | 20170103 |  |
|   21 |   11 |       13 | 20170103 |  |
|   22 |   18 |       16 | 20170103 |  |
|   23 |   19 |       17 | 20170103 |  |
|   24 |   11 |       13 | 20170103 |  |
+------+------+----------+----------+--+

我只想从选定范围之间的日期中获取值，或者从选定日期到文件结束的范围 - 基于日期的数据库中的最后一行（因此最后一天可能是例如 20210131）。我想将它们放在一个结果表中，如上所示。

@EDIT 更改后：

输出 ：

+------+-----------+-----------+----------+
| Hour |   Real    | Forecast  | Workdate |
+------+-----------+-----------+----------+
| 20   | 11.831587 | 15.140129 | 20170101 |
| 21   | 11.659364 | 15.003950 | 20170101 |
| 22   | 11.111199 | 14.736179 | 20170101 |
| 23   | 11.075579 | 14.812968 | 20170101 |
| NULL | NULL      | NULL      | NULL     |
| 1    | 9.930323  | 12.856905 | 20170102 |
| 2    | 9.826946  | 12.741908 | 20170102 |
+------+-----------+-----------+----------+

标签： sqltsql

解决方案

@Pejczi，我已经为你做了这个逻辑。您需要一个 CTE 来构建您感兴趣的所有日期。然后使用外部应用连接表函数 - 这确保将有效日期传递给函数，从而返回每个日期的小时和预测/实际列。让我知道事情的后续：

    DECLARE @StartDate DATE='20170101'
    DECLARE @EndDate DATE='20180601'--current_timestamp


    DECLARE @Dates TABLE(
          Workdate DATE  Primary Key
    )

    ;WITH Dates AS(
          SELECT Workdate=@StartDate
          UNION ALL
          SELECT CurrDate=DateAdd(DAY,1,Workdate) FROM Dates WHERE Workdate<@EndDate
    )


    SELECT *

    FROM 
        Dates D
        OUTER APPLY 
            (
             SELECT Hour, SUM(CAST(UsForecast AS DECIMAL(19, 6))) / 1000000 as Real, Day as WorkDate
             FROM GetDailyUsageReal(CONVERT(CHAR(8),D.Workdate,112),@par1,@par2)
             GROUP BY 
             Hour,Day
            )R

        OUTER APPLY 
            (
             SELECT Hour, SUM(CAST(UsForecast AS DECIMAL(19, 6))) / 1000000 as Forecast, Day as WorkDate
             FROM GetDailyUsageForecast(CONVERT(CHAR(8),D.Workdate,112),@par1,@par2)
             GROUP BY 
             Hour,Day
            )F
    ORDER BY
    d.Workdate

 option (maxrecursion 0);

推荐阅读