英文:
Access to a CTE in my whole stored procedure SQL
问题
我有一个存储过程，在其中有一个表显示从明天开始到我的记录中的最后日期的所有工作日，它运行正常，但问题是我无法在我的存储过程中访问它，我需要将它设置为全局并只需调用它，这是我的包含所有工作日的表：
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT StartDate as workingDays
FROM dateCTE
WHERE wkday NOT IN(1,7) -- 排除周六、周日
option (maxrecursion 0)
在这里，我只需要说日期是否在工作日中：
select wrkTransport.WORKSHOPID from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID ) and wrk.WORKSHOPGROUPID is not null)
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY where ASSYDATE in (**workingDays**))
但是我收到了无效的列名错误，如何使它全局并可以从存储过程的所有部分访问？
非常感谢您的提前帮助。
英文:
I have a stored procedure in which I have a table shows all working days starting from tomorrow to the last date in my records, it works fine, but the problem is I can't have access to it in my SP, I need to make it global and just call it, here is my table contains all the working days:
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT StartDate as workingDays
FROM dateCTE
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun
option (maxrecursion 0)
and here I need just to say if the dates are in WorkingDays:
select wrkTransport.WORKSHOPID from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID ) and wrk.WORKSHOPGROUPID is not null)
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY where ASSYDATE in (**workingDays**))
But I get invalid column name, how can I make it global and have access from all parts of my SP?
Thanks a lot in advance
答案1
得分: 0
以下是翻译后的代码部分：
当您创建一个CTE时，它已经完成，使用下面的代码可以获得一个临时表，您可以使用它。
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT StartDate as workingDays
INTO #name_of_temp_table
FROM dateCTE
WHERE wkday NOT IN(1,7) -- 排除周六、周日
option (maxrecursion 0);
select wrkTransport.WORKSHOPID
from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID
and wrk.WORKSHOPGROUPID is not null
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY
where ASSYDATE in (SELECT workingDays FROM #name_of_temp_table))
或者您可以创建另一个CTE并像这样使用它：
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
), workingdays as(
SELECT StartDate as workingDays
FROM dateCTE
WHERE wkday NOT IN(1,7) -- 排除周六、周日
)
select wrkTransport.WORKSHOPID
from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID
and wrk.WORKSHOPGROUPID is not null
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY
where ASSYDATE in (SELECT workingDays FROM workingdays))
英文:
When you make a CTE it is completed, with the code below you get an temporary table that you can use
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT StartDate as workingDays
INTO #name_of_temp_table
FROM dateCTE
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun
option (maxrecursion 0);
select wrkTransport.WORKSHOPID
from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID
and wrk.WORKSHOPGROUPID is not null
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY
where ASSYDATE in (SELECT workingDays FROM #name_of_temp_table))
or you make another CTE and use that like
DECLARE @StartDate AS DATE = current_timestamp
, @EndDate AS DATE = (select max(ASSYDAY.ASSYDATE) from ASSYDAY)
;WITH dateCTE AS
(
SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @EndDate
), workingdays as(
SELECT StartDate as workingDays
FROM dateCTE
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun
)
select wrkTransport.WORKSHOPID
from WORKSHOPTRANSPORT wrkTransport
join AddCapacityToCalenders addCapa
on wrkTransport.FACTORYID=addCapa.FACTORYID
and wrk.WORKSHOPGROUPID is not null
and wrkCalender.DAY>=(select MIN(ASSYDAY) from ASSYDAY
where ASSYDATE in (SELECT workingDays FROM workingdays))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论