WynneIT

I have a stored procedure that looks for orders within a date period. How do I set the date within the stored procedure? Here is my code. Thank you!! USE [TMWSUITE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Proc [dbo].[DawgAlert_Completed_Order_Status] AS SELECT orderheader.ord_number ,orderheader.ord_billto ,orderheader.ord_status ,orderheader.ord_origin_earliestdate ,orderheader.ord_origin_latestdate ,orderheader.ord_dest_earliestdate ,orderheader.ord_dest_latestdate ,orderheader.ord_trailer ,orderheader.ord_totalmiles ,stops.stp_city ,stops.stp_state ,stops.stp_schdtearliest ,stops.stp_schdtlatest ,stops.stp_status ,stops.stp_number ,stops.stp_sequence ,stops.stp_arrivaldate ,stops.stp_departuredate ,stops.cmd_code ,stops.cmp_name ,orderheader.ord_refnum ,orderheader.ord_description ,trailerprofile.trl_number ,trailerprofile.trl_type3 ,city.cty_nmstct ,orderheader.ord_completiondate FROM orderheader INNER JOIN stops ON orderheader.ord_hdrnumber = stops.ord_hdrnumber LEFT OUTER JOIN company ON orderheader.ord_company = company.cmp_id INNER JOIN trailerprofile ON orderheader.ord_trailer = trailerprofile.trl_number INNER JOIN city ON stops.stp_city = city.cty_code WHERE stops.stp_departure_status = 'DNE' AND orderheader.ord_status='CMP' AND (orderheader.ord_completiondate >= @LoadDate) AND (orderheader.ord_completiondate <= @EndDate) AND (orderheader.ord_billto = @billto)

I have a stored procedure that I have scheduled as a job to run nightly at midnight. I want the stored procedure to insert the data into a table when it runs. Do I have to create the table first or is there a way to create the table when the stored procedure runs? My second question is: I then need a job to purge that same table every night at 11pm prior to the stored procedure running again at midnight. Thank you for any help you can provide. I appreciate it. Here is my Stored Procedure: USE [TMWSUITE] GO /****** Object: StoredProcedure [dbo].[Wynne_DriverSnapshot] Script Date: 10/22/2019 6:35:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[Wynne_DriverSnapshot] as WITH cte (mpp_id, mpp_firstname, mpp_lastname, mpp_status, mpp_fleet, mpp_terminal) as ( Select mpp_id, mpp_firstname, mpp_lastname, mpp_status, mpp_fleet, mpp_terminal from manpowerprofile where mpp_terminationdt >= GETDATE() ), cte2 (lgh_number, lgh_startdate, lgh_enddate, lgh_outstatus, rn, lgh_driver1, lgh_class, lgh_class2) as ( Select lgh_number, lgh_startdate, lgh_enddate, lgh_outstatus, ROW_NUMBER() OVER (PARTITION by lgh_driver1 order by lgh_number desc) as rn, lgh_driver1, lgh_class1, lgh_class2 From legheader ) Select mpp_id, mpp_firstname, mpp_lastname, mpp_status, mpp_fleet, mpp_terminal, lgh_number, lgh_startdate, lgh_enddate, lgh_outstatus, lgh_driver1, lgh_class, lgh_class2 from cte left join cte2 on mpp_id = lgh_driver1 where rn = '1' and mpp_id NOT IN ('UNKNOWN','TESDR','TESDR2') order by mpp_id