I have created a stored procedure that is meant to create a temporary table and insert monthly periods.
我创建了一个存储过程，用于创建临时表并插入每月句点。
Stored Procedure:
存储过程：
CREATE PROCEDURE sprPeriod
@Number FLOAT, @end_date DATETIME
AS
BEGIN
IF OBJECT_ID('tempdb..#WorkingDays') IS NOT NULL
DROP TABLE #WorkingDays
CREATE TABLE #WorkingDays
(Day_Name VARCHAR(10), Day_Nr FLOAT, FromDate DATETIME, ToDate DATETIME)
INSERT INTO
#WorkingDays (Day_Name, Day_Nr, FromDate, ToDate)
VALUES
('Monday', -7, DATEADD(mm,-1,DATEADD(dd,1,@end_date)), @end_date),
('Tuesday', -6, DATEADD(mm,-1,DATEADD(dd,1,@end_date)), @end_date),
('Wednesday', -5, DATEADD(mm,-1,DATEADD(dd,1,@end_date)), @end_date),
('Thursday', -4, DATEADD(mm,-1,DATEADD(dd,1,@end_date)), @end_date),
('Friday', -3, DATEADD(mm,-1,DATEADD(dd,1,@end_date)), @end_date)
WHILE @Number >
(
SELECT
DATEDIFF(mm,MIN(WD.FromDate),MAX(WD.ToDate))+1
FROM
#WorkingDays WD
)
BEGIN
INSERT INTO
#WorkingDays (Day_Name, Day_Nr, FromDate, ToDate)
VALUES
('Monday', -7, (SELECT DATEADD(mm,-1,MIN(WD.FromDate)) FROM #WorkingDays WD), (SELECT DATEADD(dd, -1,MIN(WD.FromDate)) FROM #WorkingDays WD)),
('Tuesday', -6, (SELECT DATEADD(mm,-1,MIN(WD.FromDate)) FROM #WorkingDays WD), (SELECT DATEADD(dd, -1,MIN(WD.FromDate)) FROM #WorkingDays WD)),
('Wednesday', -5, (SELECT DATEADD(mm,-1,MIN(WD.FromDate)) FROM #WorkingDays WD), (SELECT DATEADD(dd, -1,MIN(WD.FromDate)) FROM #WorkingDays WD)),
('Thursday', -4, (SELECT DATEADD(mm,-1,MIN(WD.FromDate)) FROM #WorkingDays WD), (SELECT DATEADD(dd, -1,MIN(WD.FromDate)) FROM #WorkingDays WD)),
('Friday', -3, (SELECT DATEADD(mm,-1,MIN(WD.FromDate)) FROM #WorkingDays WD), (SELECT DATEADD(dd, -1,MIN(WD.FromDate)) FROM #WorkingDays WD))
END
END;
I then attempt the following:
然后我尝试以下方法：
EXEC sprPeriod '12', '31 July 2015'
SELECT * FROM #WorkingDays
But receive the following error:
但是收到以下错误：
Msg 208, Level 16, State 0, Line 2
Invalid object name '#WorkingDays'.
I am not sure why the temporary database cannot be retrieved. I have tried removing the DROP TABLE section but I purposively put this before the loop so it didn't drop it by mistake.
我不确定为什么无法检索临时数据库。我已经尝试删除DROP TABLE部分，但我故意将它放在循环之前，所以它不会错误地删除它。
What do I need to change in order to query the temporary database?
为了查询临时数据库，我需要更改什么？
2 个解决方案
#1
1
problem is in your SELECT query as pointed below. You can't access the local temporary table outside the scope of stored procedure cause it doesn't exists anymore. If you really want to access then consider using a Global temporary table of the form ##WorkingDays or a non temporary persistent table
问题出在您的SELECT查询中，如下所示。您无法访问存储过程范围之外的本地临时表，因为它不再存在。如果您确实想要访问，请考虑使用## WorkingDays形式的Global临时表或非临时持久表
EXEC sprPeriod '12', '31 July 2015'
SELECT * FROM #WorkingDays // Here
#2
3
Temporary tables created inside the procedure are dropped automatically when the procedure ends. If you need to have the table outside the procedure, you'll have to create it before you call the procedure, or you need to return the data with select in the procedure and use insert into #xxx exec procedurename.
过程结束时，将自动删除在过程内创建的临时表。如果需要在程序外部使用表，则必须在调用过程之前创建表，或者需要在过程中使用select返回数据并使用insert into #xxx exec procedurename。
#1
1
problem is in your SELECT query as pointed below. You can't access the local temporary table outside the scope of stored procedure cause it doesn't exists anymore. If you really want to access then consider using a Global temporary table of the form ##WorkingDays or a non temporary persistent table
问题出在您的SELECT查询中，如下所示。您无法访问存储过程范围之外的本地临时表，因为它不再存在。如果您确实想要访问，请考虑使用## WorkingDays形式的Global临时表或非临时持久表
EXEC sprPeriod '12', '31 July 2015'
SELECT * FROM #WorkingDays // Here
#2
3
Temporary tables created inside the procedure are dropped automatically when the procedure ends. If you need to have the table outside the procedure, you'll have to create it before you call the procedure, or you need to return the data with select in the procedure and use insert into #xxx exec procedurename.
过程结束时，将自动删除在过程内创建的临时表。如果需要在程序外部使用表，则必须在调用过程之前创建表，或者需要在过程中使用select返回数据并使用insert into #xxx exec procedurename。