Jump to content
Sign in to follow this  
WynneIT

Setting Date in Stored Procedure

Recommended Posts

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)

 

Share this post


Link to post
Share on other sites

Pass the dates as parameters to the procedure:

CREATE Proc [dbo].[DawgAlert_Completed_Order_Status] (@LoadDate datetime, @EndDate datetime, @billto varchar(100)) 

You may call the procedure with parameters:

exec [dbo].[DawgAlert_Completed_Order_Status] '2019/01/01','2019/01/02','bill to address'

For more info:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...