问题
我必须创建一个存储过程来向名为 #TempPayPeriod 的临时表添加新的付款期(增加 7 天)。此表包含 PayPeriod 表中的所有付款期间数据。所以我创建了一个程序,将这些永久数据全部移动到这个临时表中,并向那个临时表添加一个新的付款周期。
现在我需要查看该临时表,以便将其与该数据进行比较并确保它匹配
在我的代码中,我在执行过程后立即编写了 SELECT * FROM #TempPayPeriod。我成功运行命令但没有要查看的结果集?
- USE PR
- GO
- CREATE PROC spAddPay
- @StartDate smalldatetime = NULL,
- @EndDate smalldatetime = NULL
- AS
- DROP TABLE IF EXISTS #TempPayPeriod;
- SELECT * INTO #TempPayPeriod FROM PayPeriod;
- SET @StartDate = (SELECT MAX(PerFrom) FROM #TempPayPeriod);
- SET @StartDate = DATEADD(day, 7, @StartDate);
- SET @EndDate = (SELECT MAX(PerThru) FROM #TempPayPeriod);
- SET @EndDate = DATEADD(day, 7, @EndDate);
- BEGIN TRY
- INSERT #TempPayPeriod
- VALUES (@StartDate, @EndDate);
- END TRY
- BEGIN CATCH
- PRINT 'Record was not added';
- PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
- END CATCH
- EXEC spAddPay;
- SELECT * FROM #TempPayPeriod;
- GO
复制代码
回答
存储过程完成后,临时表超出范围。您需要将 select 语句移动到存储过程中,或者使用双井号表示法使临时表成为全局表。 ##TempPayPeriod
选项 1 - 从存储过程返回结果
- USE PR
- GO
- CREATE PROC spAddPay
- @StartDate smalldatetime = NULL,
- @EndDate smalldatetime = NULL
- AS
- BEGIN
- DROP TABLE IF EXISTS #TempPayPeriod;
- SELECT * INTO #TempPayPeriod FROM PayPeriod;
- SET @StartDate = (SELECT MAX(PerFrom) FROM #TempPayPeriod);
- SET @StartDate = DATEADD(day, 7, @StartDate);
- SET @EndDate = (SELECT MAX(PerThru) FROM #TempPayPeriod);
- SET @EndDate = DATEADD(day, 7, @EndDate);
- BEGIN TRY
- INSERT #TempPayPeriod
- VALUES (@StartDate, @EndDate);
- END TRY
- BEGIN CATCH
- PRINT 'Record was not added';
- PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
- END CATCH
- SELECT * FROM #TempPayPeriod
- END
- GO
- EXEC spAddPay;
- GO
复制代码
选项 2 - 使用全局临时表
- USE PR
- GO
- CREATE PROC spAddPay
- @StartDate smalldatetime = NULL,
- @EndDate smalldatetime = NULL
- AS
- BEGIN
- DROP TABLE IF EXISTS ##TempPayPeriod;
- SELECT * INTO ##TempPayPeriod FROM PayPeriod;
- SET @StartDate = (SELECT MAX(PerFrom) FROM ##TempPayPeriod);
- SET @StartDate = DATEADD(day, 7, @StartDate);
- SET @EndDate = (SELECT MAX(PerThru) FROM ##TempPayPeriod);
- SET @EndDate = DATEADD(day, 7, @EndDate);
- BEGIN TRY
- INSERT ##TempPayPeriod
- VALUES (@StartDate, @EndDate);
- END TRY
- BEGIN CATCH
- PRINT 'Record was not added';
- PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
- END CATCH
- END
- EXEC spAddPay;
- SELECT * FROM ##TempPayPeriod;
- GO
复制代码
|