访问我整个存储过程中的CTE的SQL。

huangapple
go评论57阅读模式
英文:

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))

huangapple
  • 本文由 发表于 2023年7月27日 22:29:47
  • 转载请务必保留本文链接：https://go.coder-hub.com/76780754.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定