Jump to content

Setting Date in Stored Procedure


WynneIT
 Share

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)

 

Link to comment
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

 

Link to comment
Share on other sites

  • 3 months later...

Hello, @WynneIT

Please try this code,To set date in stored procedure

CREATE PROCEDURE TRGetMsgTksByBIdDIdPIdAMsreDte

@REGISTRATION_DATE DATETIME = NULL 

AS SET NOCOUNT

ON

SELECT * FROM STUDENT

WHERE REGISTRATION_DATE = @REGISTRATION_DATE RETURN 0

GO

I hope above code will be useful for you.

Thank you.

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...