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