how to access to a temporary table in my whole stored procedure SQL

July 27, 2023

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

>Solution :

when you make a cte it is completed, with the code below you get an temorarry table tha 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))