SQL Server Access to a CTE in my whole stored procedure SQL

iyzzxitl 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(152)

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

sql-server

来源：https://stackoverflow.com/questions/76780754/access-to-a-cte-in-my-whole-stored-procedure-sql

1条答案

按热度按时间

dxxyhpgq1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Access to a CTE in my whole stored procedure SQL

1条答案

相关问题

热门标签

最新问答